Skip to content
FR

Querying Data with KQL and DAX

Step 13 of 20

Querying Data with KQL and DAX

Introduction

This lesson completes the "Query and analyze data" group of the domain Prepare data (45–50% of the DP-600 skills measured): query and analyze data by using KQL and DAX, in addition to SQL and the visual query editor. This course targets the English syllabus effective October 19, 2026. Microsoft does not publish the question count or the question-type mix, so OnLevelUp mock exams use a 50-question practice length and an unofficial 80% practice target, which is not equivalent to the official 700/1000 scaled passing score.

Fabric has three query languages that matter for analytics. T-SQL queries warehouses and lakehouse endpoints (previous lesson). KQL (Kusto Query Language) queries eventhouses and KQL databases, built for logs, telemetry and time series. DAX queries semantic models, and also defines their measures. The exam gives a data store and a question and asks which language and which construct to use. After this lesson you will be able to write basic KQL to select, filter and aggregate, write DAX queries with EVALUATE and SUMMARIZECOLUMNS, and choose the right language.

Learning objectives

  • Write KQL queries with where, project, extend, summarize, top, join and time bucketing.
  • Write DAX queries with EVALUATE, SUMMARIZECOLUMNS, FILTER, TOPN and ORDER BY.
  • Run DAX queries in the DAX query view and understand the difference with measures.
  • Choose between KQL, DAX and T-SQL for a given item and question.

1. Which language for which item

Item Language Typical question
Eventhouse / KQL database KQL (T-SQL subset also accepted for simple reads) Errors per 5 minutes from device telemetry
Semantic model DAX Revenue by category with filters from the model's relationships
Warehouse T-SQL Join and aggregate curated facts
Lakehouse SQL analytics endpoint T-SQL Read Delta tables
Lakehouse via Spark PySpark / Spark SQL Large transformations and ML

A KQL queryset is a Fabric item that stores KQL queries against one or more KQL databases (and can be used for dashboards and Real-Time Dashboard tiles). In an eventhouse, data is ingested into tables with time-based partitioning and indexing optimized for append-heavy streams. A semantic model is queried from the DAX query view in Power BI (web or Desktop) or from external tools through the XMLA endpoint.

2. KQL fundamentals

A KQL query is a pipeline: a table name followed by operators separated with |. Each operator receives the rows of the previous one. This resembles Power Query steps more than SQL clauses.

KQL
DeviceEvents
| where Timestamp > ago(24h)
| where Severity in ("Error", "Critical")
| project Timestamp, DeviceId, Severity, Message
| order by Timestamp desc
| take 100

Core operators:

Operator Purpose SQL analogy
where Filters rows WHERE
project / project-away Choose or drop columns SELECT list
extend Add calculated columns computed column in SELECT
summarize ... by Aggregate GROUP BY
top N by col Top rows by a column ORDER BY ... TOP
order by (alias sort by) Sort ORDER BY
take (alias limit) Any N rows, no guaranteed order TOP without order
join kind=... Combine tables JOIN
union Stack tables UNION ALL
distinct Distinct values DISTINCT
count Row count COUNT(*)
let Variables and reusable expressions CTE or variable

Filtering operators on strings are an exam favorite. They differ in case sensitivity and performance:

Operator Meaning
== Equals, case-sensitive
=~ Equals, case-insensitive
has Contains a whole term, case-insensitive, index-based and fast
contains Contains a substring, case-insensitive, slower
startswith Starts with
in (...) In a list
!has, !contains Negated forms

Prefer has over contains when you search for a complete term, because the engine uses its term index and scans less data. Put time filters first (where Timestamp > ago(1h)), since tables are partitioned by ingestion time and the engine skips extents outside the range.

3. Aggregating and bucketing time in KQL

KQL
DeviceEvents
| where Timestamp between (datetime(2026-10-01) .. datetime(2026-10-08))
| summarize Events = count(),
            Devices = dcount(DeviceId),
            ErrorRate = round(100.0 * countif(Severity == "Error") / count(), 2)
          by bin(Timestamp, 1h), Region
| order by Timestamp asc
  • count() counts rows, dcount() approximates distinct counts (fast and accurate enough), countif() and sumif() aggregate under a condition, avg, min, max, percentile(col, 95), arg_max(col, *) returns the row with the maximum value, make_list/make_set collect values.
  • bin(Timestamp, 1h) rounds timestamps into buckets so you can group by time. It is the KQL equivalent of grouping by a date part.
  • between (a .. b) is inclusive at both ends. ago(1d) is relative to now.
  • arg_max(Timestamp, *) by DeviceId returns the latest event per device, a common "current state" query.
KQL
DeviceEvents
| summarize arg_max(Timestamp, *) by DeviceId

render timechart after a summarize ... by bin(Timestamp, ...) draws a time chart in the KQL queryset, useful for exploration and for Real-Time Dashboard tiles.

4. Joins, let and parsing in KQL

KQL's default join is innerunique, which deduplicates the left side's join keys first. This is a classic trap: the result can have fewer rows than expected. Specify the kind explicitly.

KQL
let Devices = DeviceInventory | project DeviceId, Site, Model;
DeviceEvents
| where Timestamp > ago(7d)
| join kind=inner (Devices) on DeviceId
| summarize Errors = countif(Severity == "Error") by Site

Other kinds: leftouter, rightouter, fullouter, leftanti, leftsemi. Put the smaller table on the right side of the join for better performance, and filter before joining.

Parsing semi-structured text is common: parse, extract, parse_json (to dynamic type), todynamic, mv-expand to expand arrays into rows.

KQL
Logs
| extend p = parse_json(Payload)
| project Timestamp, Level = tostring(p.level), Latency = toint(p.latencyMs)

Example. Fabrikam Logistics streams GPS pings from 8,000 trucks into an eventhouse through an eventstream. Dispatch wants the last known position of every truck and the number of pings per truck over the last hour. Two KQL queries suffice: summarize arg_max(Timestamp, *) by TruckId for the last position, and where Timestamp > ago(1h) | summarize Pings = count() by TruckId for the activity count.

5. Querying with T-SQL in an eventhouse

A KQL database also accepts a limited T-SQL subset for reading, and you can translate with the explain keyword (EXPLAIN SELECT ...) to see the KQL equivalent. It is useful for analysts who know SQL, but KQL has more features for time series (series_* functions, anomaly detection with series_decompose_anomalies, make-series), so the complex analytical questions in the exam point to KQL.

6. DAX fundamentals

DAX (Data Analysis Expressions) is the language of Power BI semantic models. It has two roles:

  1. Define calculations inside the model (measures, calculated columns, calculated tables).
  2. Query the model: a DAX query always starts with EVALUATE and returns a table.

DAX queries are used in the DAX query view (a Power BI Desktop and web feature that lets you write, run and save queries against the model), by tools through the XMLA endpoint or the REST API executeQueries, and by Copilot suggestions. Running a DAX query does not change the model; only commands such as "update model with changes" apply a measure definition made in a query to the model.

The basic query shape:

DAX
DEFINE
    MEASURE Sales[Revenue] = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Year],
    'Product'[Category],
    FILTER ( VALUES ( 'Product'[Category] ), 'Product'[Category] <> "Services" ),
    "Revenue", [Revenue],
    "Orders", DISTINCTCOUNT ( Sales[OrderID] )
)
ORDER BY 'Date'[Year], [Revenue] DESC

Parts of a DAX query:

Part Role
DEFINE (optional) Declares query-scoped MEASURE, VAR, COLUMN, TABLE that exist only for this query
EVALUATE (required) The table expression whose result is returned
ORDER BY (optional) Sorts the result
START AT (optional) Starts the sorted result at a given value

Key table functions:

Function Purpose
SUMMARIZECOLUMNS Group by columns from related tables and add named calculations; the standard function for queries. Removes groups where all calculations are blank.
SUMMARIZE Group by columns of one table; avoid adding calculations inside it, use ADDCOLUMNS instead
ADDCOLUMNS Adds calculated columns to a table expression
SELECTCOLUMNS Projects and renames columns
FILTER Row-by-row filter of a table expression
TOPN Top N rows by an expression
VALUES / DISTINCT Distinct values of a column
ROW Single-row table, handy for one-off scalar results
UNION, INTERSECT, EXCEPT Set operations on tables
CALCULATETABLE Evaluates a table expression in a modified filter context

CALCULATE and CALCULATETABLE are the engine of DAX: they change the filter context in which an expression is evaluated. Row context, filter context and context transition are developed in the advanced DAX lesson.

DAX
EVALUATE
TOPN (
    10,
    SUMMARIZECOLUMNS (
        'Product'[ProductName],
        "Revenue", SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )
    ),
    [Revenue], DESC
)
ORDER BY [Revenue] DESC

Note that TOPN itself does not guarantee the output order; add ORDER BY to sort the result.

7. SQL, KQL and DAX side by side

The same question, "total revenue by year":

Language Query
T-SQL SELECT d.Year, SUM(s.Amount) AS Revenue FROM Sales s JOIN Date d ON d.DateKey = s.DateKey GROUP BY d.Year
KQL Sales | summarize Revenue = sum(Amount) by Year = getyear(OrderDate)
DAX EVALUATE SUMMARIZECOLUMNS ('Date'[Year], "Revenue", SUM ( Sales[Amount] ))

Important differences:

  • T-SQL needs explicit joins; DAX uses the relationships of the model, so you reference columns of related tables directly.
  • KQL has no joins in the common case: telemetry is stored denormalized in one wide table, and summarize does the work.
  • DAX measures are evaluated at query time in a filter context; T-SQL aggregates are computed by the SQL clause.

Worked example 7: choosing the language and writing the query. Statement. Contoso Energy ingests smart-meter readings (about 40 million per day) into an eventhouse table Readings(Timestamp, MeterId, Region, kWh). Operations want a chart of average consumption per region per hour for the last 48 hours. Separately, finance has a semantic model with Sales and Product and wants the top five product categories by revenue for 2026. A third analyst asks whether a warehouse view can be used for the meter chart. Reasoning. (1) The meter data is a high-volume time series in an eventhouse, so KQL is the natural language: filter on time, summarize avg(kWh) by bin(Timestamp, 1h), Region. (2) The finance question targets a semantic model and its measures, so a DAX query is right: TOPN over a SUMMARIZECOLUMNS by category, filtered to 2026. (3) The warehouse view would require copying data from the eventhouse; it could work but adds latency and cost, and KQL already supports the question directly. Solution.

KQL
Readings
| where Timestamp > ago(48h)
| summarize AvgKwh = avg(kWh) by bin(Timestamp, 1h), Region
| render timechart
DAX
EVALUATE
TOPN (
    5,
    SUMMARIZECOLUMNS (
        'Product'[Category],
        FILTER ( ALL ( 'Date'[Year] ), 'Date'[Year] = 2026 ),
        "Revenue", SUM ( Sales[Amount] )
    ),
    [Revenue], DESC
)
ORDER BY [Revenue] DESC

Why the other choices are worse. T-SQL on a warehouse copy adds a pipeline for no gain. Spark would work but is overkill for an ad-hoc time-series chart. Loading 40 million rows per day into an Import-mode semantic model would be costly and stale.

8. Choosing the right query language

Scenario Best choice
Streaming telemetry, logs, time-series exploration, anomaly detection KQL on an eventhouse
Business metrics with relationships, time intelligence, row-level security rules DAX on a semantic model
Relational joins, set-based transformations, stored procedures T-SQL on a warehouse
Read-only SQL over lakehouse Delta tables T-SQL on the SQL analytics endpoint
Large-scale data engineering, ML PySpark in a notebook
User wants no code Visual query editor

A scenario often adds a skill statement ("the team knows SQL"). If the volume allows it, answer with the language they already use. If the data is in an eventhouse, however, KQL's operators for time series are usually the expected answer.

Common pitfalls

  • Using contains instead of has for whole-term searches in KQL (slower).
  • Forgetting that KQL's default join is innerunique and loses duplicate left keys.
  • Putting where Timestamp after expensive operators instead of first.
  • Using take and assuming it returns the top rows (it returns arbitrary rows).
  • Using SUMMARIZE with calculations; prefer SUMMARIZECOLUMNS or ADDCOLUMNS.
  • Forgetting EVALUATE in a DAX query, or expecting TOPN output to be sorted without ORDER BY.
  • Confusing a measure defined with DEFINE (query scope) with a model measure (persisted).
  • Using SUMMARIZECOLUMNS inside a measure when a filter context exists: it is not supported with some row or filter contexts, so use SUMMARIZE/ADDCOLUMNS patterns in measures.

Exam tip: "latest status per device", "events in the last hour", "errors per 5-minute bin" → KQL (arg_max, ago, bin). "Query the semantic model", "return a table of measures by category", "DAX query view" → EVALUATE with SUMMARIZECOLUMNS. Check the item type before reading the options.

Mini case study

Northwind Trading has a retail semantic model (Sales, Product, Date, Store) and an eventhouse receiving point-of-sale heartbeat events (Heartbeat(Timestamp, StoreId, TerminalId, Status)). Management wants (1) to know which terminals have not sent a heartbeat for 10 minutes, (2) the monthly revenue and order count by store region for 2026 in a table to export, and (3) a check on how many terminals were in "Offline" status per hour yesterday.

Proposed solution.

  1. KQL, latest heartbeat per terminal, then compare with now():
KQL
Heartbeat
| summarize arg_max(Timestamp, *) by TerminalId
| where Timestamp < ago(10m)
| project TerminalId, StoreId, LastSeen = Timestamp
  1. DAX query in the DAX query view:
DAX
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[MonthNumber], 'Date'[MonthName], 'Store'[Region],
    FILTER ( ALL ( 'Date'[Year] ), 'Date'[Year] = 2026 ),
    "Revenue", SUM ( Sales[Amount] ),
    "Orders", DISTINCTCOUNT ( Sales[OrderID] )
)
ORDER BY 'Date'[MonthNumber], 'Store'[Region]
  1. KQL with where Timestamp >= startofday(ago(1d)) and Timestamp < startofday(now()), where Status == "Offline", summarize Offline = dcount(TerminalId) by bin(Timestamp, 1h).

Commentary. Each request used the language of its item. Two traps were avoided: using take for "latest" (it returns arbitrary rows) and trying to query the eventhouse from DAX, which only queries semantic models.

Key terms

  • KQL: Kusto Query Language, pipeline syntax for eventhouses and KQL databases.
  • Eventhouse: Fabric item hosting one or more KQL databases for real-time and time-series data.
  • KQL queryset: item that stores KQL queries.
  • bin(): rounds values into buckets, typically for time grouping.
  • arg_max: returns the row with the maximum value of an expression.
  • DAX query: a statement beginning with EVALUATE that returns a table from a semantic model.
  • SUMMARIZECOLUMNS: DAX function that groups and calculates, preferred for queries.
  • DAX query view: editor for running DAX queries against a semantic model.
  • Filter context: the set of filters applied when a DAX expression is evaluated.

Summary checklist

  • I can write a KQL pipeline with where, project, extend, summarize and bin.
  • I know why has is preferred to contains and why time filters come first.
  • I can return the latest row per key with arg_max.
  • I can write a DAX query with EVALUATE, SUMMARIZECOLUMNS, TOPN and ORDER BY.
  • I can tell a query-scoped measure from a model measure.
  • I can pick between KQL, DAX, T-SQL, Spark and the visual query editor from the item type and the scenario.
  • I remember that the practice length (50 questions) and 80% target are not official, and that the course targets the syllabus effective October 19, 2026.

Official sources