Query with KQL and DAX

What it is

KQL (Kusto Query Language) queries KQL databases in an Eventhouse. It is built for large volumes of time-stamped data such as logs and telemetry, and you write it in a KQL queryset or directly in the database.

DAX queries a semantic model. Besides writing measures, you can run whole DAX queries in DAX query view in Power BI Desktop or the Fabric web editor, or in tools such as DAX Studio over the XMLA endpoint.

How it works

KQL: a pipeline of operators

A KQL query starts with a table and passes it through operators separated by |. Each operator works on the output of the one before.

SensorReadings
| where Timestamp > ago(1h)
| where DeviceType == "Thermostat"
| summarize AvgTemp = avg(Temperature), Readings = count()
    by DeviceId, bin(Timestamp, 5m)
| order by AvgTemp desc

bin(Timestamp, 5m) groups times into five-minute buckets, and ago(1h) means one hour before now. Adding | render timechart draws the result as a chart.

KQL details the exam likes

• Names and the == operator are case-sensitive; =~ compares strings without case.
• has matches a whole term and uses the index; contains matches any substring and is slower.
• The default join kind is innerunique, which keeps only one row per key from the left table. Write kind=inner when you need every match.

Readings
| join kind=inner (Devices | project DeviceId, Building) on DeviceId
| summarize Readings = count() by Building
| top 5 by Readings desc

DAX queries

A DAX query starts with EVALUATE and must return a table. SUMMARIZECOLUMNS is the usual way to group and aggregate, and it is the function Power BI visuals generate.

EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Category],
    FILTER ( VALUES ( 'Date'[Year] ), 'Date'[Year] = 2026 ),
    "Total Sales", [Total Sales]
)
ORDER BY [Total Sales] DESC

DEFINE adds measures or variables that exist only for the query:

DEFINE
    MEASURE Sales[Avg Order Value] =
        DIVIDE ( [Total Sales], DISTINCTCOUNT ( Sales[OrderID] ) )
EVALUATE
TOPN (
    5,
    SUMMARIZECOLUMNS (
        'Customer'[Customer Name],
        "Sales", [Total Sales],
        "AOV", [Avg Order Value]
    ),
    [Sales], DESC
)
ORDER BY [Sales] DESC

To return a single value, wrap it in a table constructor: EVALUATE { [Total Sales] }.

DAX query view

DAX query view lets you write and run queries, and right-click a table or measure for quick queries such as showing the top rows or defining all measures. A measure defined in a query can be added to the model from the query view, which makes it a safe place to test a measure first.

Because a DAX query runs against the model, it uses the model's relationships and measures, and row-level security applies to users who belong to a role. The same question asked in SQL against the warehouse would need its own joins and could give different totals if the measure logic differs.

Which language for which store

A worked example

An operations team stores thermostat telemetry in an Eventhouse and has a semantic model of building costs.

1. To find devices whose average temperature in the last hour is above 26, run where Timestamp > ago(1h), then summarize avg(Temperature) by DeviceId, then where on the average.
2. To check a new "cost per square metre" measure before adding it to the model, define it in DAX query view and evaluate it in a SUMMARIZECOLUMNS query.
3. When the numbers match the finance team's figures, add the measure to the model from the query view.

How the exam asks about this

• "Count events in 15-minute intervals over the last day." where Timestamp > ago(1d) | summarize count() by bin(Timestamp, 15m).
• "Return every matching row from both tables in a KQL join." Use join kind=inner, not the default innerunique.
• "Test a measure against the model without changing the model." Define it with DEFINE MEASURE in DAX query view.