Querying with DAX
Use DAX as a query language — EVALUATE, SUMMARIZECOLUMNS, and CALCULATETABLE. Query semantic models, inspect measure results, and understand DAX evaluation context.
DAX as a query language
Think of DAX queries like asking a smart assistant very specific questions.
You already know DAX creates measures in Power BI (like SUM, AVERAGE, CALCULATE). But DAX can also be used as a query language — you write a query, it returns a table of results, like SQL does.
Why would you use DAX queries instead of SQL? Because DAX queries evaluate inside the semantic model — they use the relationships, measures, and business logic you have already defined. SQL queries hit the raw tables and know nothing about your model.
The EVALUATE statement
Every DAX query begins with EVALUATE followed by a table expression:
// Simple: return the entire Products table
EVALUATE Products
// With columns: return specific columns
EVALUATE
SELECTCOLUMNS(
Products,
"Product Name", Products[ProductName],
"Category", Products[Category],
"Unit Price", Products[UnitPrice]
)EVALUATE with filtering
// Filter to products in the "Fresh Produce" category
EVALUATE
FILTER(
Products,
Products[Category] = "Fresh Produce"
)EVALUATE with aggregation
// Total revenue by region — using SUMMARIZECOLUMNS
EVALUATE
SUMMARIZECOLUMNS(
Stores[Region],
"Total Revenue", [Total Revenue],
"Transaction Count", [Transaction Count]
)What’s happening: SUMMARIZECOLUMNS groups by Stores[Region] and evaluates the [Total Revenue] and [Transaction Count] measures for each group. The measures use the semantic model’s relationships — no explicit JOINs needed.
Key DAX query functions
| Function | What It Does | Example |
|---|---|---|
EVALUATE | Returns a table (required to start a DAX query) | EVALUATE Sales |
SUMMARIZECOLUMNS | Group by columns and evaluate measures | Revenue by region |
CALCULATETABLE | Return a filtered table | Sales where amount > 1000 |
SELECTCOLUMNS | Choose specific columns from a table | Project name and price only |
FILTER | Row-by-row filter on a table | Products where price > 50 |
TOPN | Return top N rows | Top 10 products by revenue |
ORDER BY | Sort the EVALUATE result | Sort by revenue descending |
Complete query example
// Top 10 stores by revenue in Q1 2026
DEFINE
MEASURE Sales[Q1 Revenue] =
CALCULATE(
[Total Revenue],
Dates[Quarter] = "Q1",
Dates[Year] = 2026
)
EVALUATE
TOPN(
10,
ADDCOLUMNS(
VALUES(Stores[StoreName]),
"Q1 Revenue", [Q1 Revenue]
),
[Q1 Revenue], DESC
)
ORDER BY [Q1 Revenue] DESCWhat’s happening line by line:
- DEFINE — create a temporary measure scoped to this query
- MEASURE — calculate revenue filtered to Q1 2026
- EVALUATE — begin the result table
- TOPN — return only the top 10 rows
- ADDCOLUMNS — start with unique store names, add the Q1 Revenue column
- ORDER BY — sort the result
Exam tip: DEFINE vs model measures
The DEFINE block lets you create temporary measures that exist only for the duration of the query. This is useful for:
- Testing measure logic before adding it to the model
- Creating complex calculations without modifying the semantic model
- Debugging — isolate a measure and evaluate it step by step
DEFINE measures override model measures with the same name (within the query only).
Where to run DAX queries
| Tool | Use Case |
|---|---|
| DAX Studio | The gold standard for DAX query development and performance tuning |
| SSMS / Azure Data Studio | Connect via XMLA endpoint to query semantic models |
| DAX Query View | Built into Power BI Desktop — author and run DAX queries directly |
| Power BI Performance Analyzer | Captures DAX queries generated by report visuals (for analysis, not authoring) |
| Fabric Notebook | Execute DAX queries programmatically using the semantic link library |
Scenario: Anita debugs a revenue measure
Anita at FreshCart notices that her Power BI dashboard shows unexpected revenue numbers for the “Metro” store type. She opens DAX Studio and runs:
EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS(
Stores[StoreName],
Stores[StoreType],
"Revenue", [Total Revenue]
),
Stores[StoreType] = "Metro"
)The query returns revenue for each Metro store, letting her compare against known values. She discovers that one store has a duplicated entry in the dimension table, causing double-counted revenue. The issue is in the data, not the measure.
DAX vs SQL: When to use which
| Factor | DAX Queries | SQL Queries |
|---|---|---|
| Evaluates against | Semantic model (relationships, measures, RLS) | Raw storage tables (lakehouse/warehouse) |
| Joins | Automatic — uses model relationships | Manual — explicit JOIN clauses |
| Best for | Testing measures, debugging reports, model-aware analysis | Data preparation, ETL, ad-hoc exploration |
| Performance tuning | DAX Studio profiling, query plans | SQL execution plans, indexing |
| Write capability | Read-only (queries only, no DML) | Full DML in warehouses |
Anita at FreshCart wants to test a new 'Year-over-Year Growth' measure against her semantic model before adding it permanently. Which approach lets her test the measure without modifying the model?
An exam question asks: 'Which DAX function returns the top N rows from a table expression based on a specified column?' What is the answer?
🎬 Video coming soon
Next up: Semantic Models: Storage Modes — Import, DirectQuery, and Direct Lake: choose the right mode for your data.