Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 35 lessons
  1. Start here: cloud, data, Fabric and Power BI basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. Power BI in plain words: semantic models, reports and DAX
  2. Prepare data: get data in (exam area 2, 45-50%)

    1. 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
    2. 7. Choosing a data store: lakehouse, warehouse or eventhouse
    3. 8. Use data without copying: shortcuts, mirroring and OneLake integration
    4. 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
    5. 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
    6. 11. Messy data: duplicates, missing values and nulls
  3. Prepare data: transform and query (exam area 2)

    1. 12. Views, functions and stored procedures in T-SQL
    2. 13. Build a star schema in a lakehouse or warehouse
    3. 14. Aggregate, join and enrich: SQL and PySpark side by side
    4. 15. Query with the Visual query editor and SQL
    5. 16. Query with KQL
    6. 17. Query with DAX: DAX query view and EVALUATE
  4. Design and build semantic models (exam area 3, 25-30%)

    1. 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
    2. 19. Relationships: star schemas, bridge tables and many-to-many
    3. 20. DAX foundations: measures, filter context, CALCULATE and variables
    4. 21. DAX functions the exam tests: iterators, table filters, windows and information functions
    5. 22. Calculation groups, dynamic format strings and field parameters
    6. 23. Composite models and large semantic model storage format
  5. Optimize models, then secure them (exam areas 3 and 1)

    1. 24. Direct Lake in depth: fallback, refresh and the two flavors
    2. 25. Incremental refresh for semantic models
    3. 26. Faster reports and DAX: finding and fixing slow queries
    4. 27. Who can see what: workspace roles and item permissions
    5. 28. Fine-grained security: rows, columns, objects and files
    6. 29. Governance: sensitivity labels and endorsement
  6. Lifecycle, then exam practice (exam area 1)

    1. 30. Version control: Git integration and Power BI projects (.pbip)
    2. 31. Deployment pipelines and impact analysis
    3. 32. XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models
    4. 33. Mock exam 1
    5. 34. Final review and exam day: every decision on one page
    6. 35. Mock exam 2
See the leaderboard

20 min

Query with DAX: DAX query view and EVALUATE

Select, filter and aggregate data with DAX queries in DAX query view.

By the end of this lesson you can

  • Open DAX query view in Power BI Desktop and in the service
  • Write EVALUATE queries that select, filter, group and rank model data
  • Read a DAX query and predict its result table
  • Test a measure with DEFINE MEASURE, then save it to the model

From earlier lessons · 1 of 3

In KQL, which operator groups rows and calculates counts or sums per group?

Pick one answer.

From earlier lessons · 2 of 3

Juniper store managers need sales that change when they pick a region in a slicer. Which DAX object fits?

Pick one answer.

From earlier lessons · 3 of 3

In T-SQL, which clause keeps only groups whose total is above a value?

Pick one answer.

Juniper's operations lead messages you: "Which two stores sold the most, and by how much?" You could build a table visual, add a filter, sort it and screenshot it. Or you could type six lines against the semantic model and get the answer as a table. That's a DAX query.

[6]

DAX query view: where queries run

Exam depth: know it cold.

A DAX query returns a table of data from a semantic model, like a SQL query. It doesn't change the model or add visuals. You write and run queries in DAX query view.

Power BI DesktopPower BI service (web)
How to open.The DAX query view icon on the left.Write DAX queries on the model's menu or details page.
You need.The model open in Desktop.Write permission on the model.
Saved?Query tabs save in the file.Tabs are discarded when you close.

In the web, the workspace setting User can edit data models in the Power BI service (preview) must also be on.

A viewer with only Build permission can't write queries in the web. They live connect from Desktop and use DAX query view there.

Select Run to execute the query, or just the lines you selected. Results appear in the Results grid. With several EVALUATE statements, the Result dropdown switches between their tables.

[191]

DAX query view in the Power BI service. The ribbon has Run and Update model with changes buttons. An EVALUATE query fills the editor, and a Results grid below shows a 4-column, 7-row table. A Query 1 tab sits at the bottom.
Microsoft Learn, CC BY 4.0

[191]

Exam depth: recognize it.

Right-click a field in the Data pane and choose Quick queries to have a query written for you.

  • On a table: Show top 100 rows or Show column statistics.
  • On a column: Show data preview, a list of its distinct values.
  • On a measure: Evaluate, which uses SUMMARIZECOLUMNS. Define and evaluate also shows its formula.

To see the query behind a report visual, open Performance Analyzer in Report view. Record, refresh visuals, then run the visual's query in DAX query view.

Limits worth knowing: 1,000,000 values per query, and 99,999 rows per query in the web.

[191]

You are an analytics engineer at Juniper Coffee. A finance analyst has Build permission on the sales semantic model, but not Write. She needs to run her own DAX queries against it. What should she do?

Pick one answer.

[191]

The shape of a DAX query

Exam depth: know it cold.

Every DAX query has at least one EVALUATE statement. EVALUATE is followed by a table expression: a table name or a function that returns a table.

  • To show one value, wrap it in curly braces, { }, or use ROW(). Both turn a value into a one-row table.
  • ORDER BY sorts the result. ASC is the default.
  • DEFINE is optional and comes first. It holds MEASURE and VAR definitions that exist only while the query runs.
  • A query can have many EVALUATE statements but only one DEFINE block.

There is no SELECT, FROM or JOIN. Relationships in the model connect the tables for you. The examples use the Juniper model from lesson 5.

[191][192]

dax
EVALUATE Store // 3 rows: Harbour, Old Mill, Riverside
EVALUATE { [Total Sales] } // 1 row: 45
EVALUATE ROW ( "Orders", COUNTROWS ( Sales ) ) // 1 row, column Orders: 6
EVALUATE Sales ORDER BY Sales[Amount] DESC // 6 rows, largest first
Four complete queries. Each EVALUATE returns its own result table.

[192]

Select and filter rows: SELECTCOLUMNS and FILTER

Exam depth: know it cold.

If you know SQL, map each idea across.

You wantSQLDAX
All rows.SELECT * FROM SalesEVALUATE Sales
Some columns, renamed.SELECT OrderID AS [Order]SELECTCOLUMNS ( Sales, "Order", Sales[OrderID] )
Filter rows.WHERE Amount >= 8FILTER ( Sales, Sales[Amount] >= 8 )
Distinct values.SELECT DISTINCT StoreDISTINCT ( Sales[Store] )

FILTER walks a table row by row and keeps the rows where the condition is true. Join two conditions with && for AND or || for OR. SELECTCOLUMNS returns only the columns you name, each with a new name in quotes.

[191]

dax
EVALUATE
FILTER ( Sales, Sales[Product] = "Muffin" && Sales[Qty] >= 2 )
// Orders 5 (Riverside, Qty 4) and 6 (Old Mill, Qty 2)
EVALUATE
SELECTCOLUMNS ( Sales, "Order", Sales[OrderID], "Amount", Sales[Amount] )
// 6 rows, 2 columns: Order and Amount
Row filters and column picks on the Juniper Sales table.

[191]

Group and aggregate: SUMMARIZECOLUMNS

Exam depth: know it cold.

SUMMARIZECOLUMNS is DAX's GROUP BY. It takes, in order:

  1. One or more group-by columns, written Table[Column].
  2. Optional filter tables that narrow the data.
  3. Pairs of a name in quotes and an expression, usually a measure.

It returns one row per combination of group-by values. Group-by columns from different tables combine through the model's relationships.

Two behaviors trip people up:

  • A row where every expression is blank is dropped. SQL's GROUP BY would keep it.
  • The order isn't guaranteed. Add ORDER BY.

To filter the groups, the way HAVING does in SQL, wrap SUMMARIZECOLUMNS in FILTER.

[191][193]

dax
EVALUATE
SUMMARIZECOLUMNS (
Store[Region],
Sales[Product],
"Region Sales", [Total Sales]
)
ORDER BY Store[Region], Sales[Product]
// North | Latte | 20 North | Muffin | 9
// South | Latte | 4 South | Muffin | 12
Sales by region and product. Total Sales is the measure SUM(Sales[Amount]).

[192][193]

Predict, then reveal

Predict the result: EVALUATE FILTER ( SUMMARIZECOLUMNS ( Sales[Store], "Store Sales", [Total Sales] ), [Store Sales] > 15 )

[191][193]

CALCULATETABLE, ADDCOLUMNS and TOPN

Exam depth: know it cold.

FunctionWhat it doesJuniper example
CALCULATETABLE ( table, filter )Evaluates a table expression under extra filters. It's the table version of CALCULATE.Region sales for Latte only.
ADDCOLUMNS ( table, "Name", expr )Returns the table with all its columns plus new calculated ones.Each region with its sales.
TOPN ( n, table, orderBy, DESC )Returns the top n rows by the order expression.The two best stores.

Two TOPN facts the exam likes:

  • TOPN doesn't sort its output. Add ORDER BY to the query.
  • On a tie at row n, TOPN returns every tied row, so you can get more than n.

The order argument defaults to descending when you leave it out.

[192][194][195][196]

dax
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS ( Store[Region], "Latte Sales", [Total Sales] ),
Sales[Product] = "Latte"
) // North 20, South 4
EVALUATE
TOPN ( 2, SUMMARIZECOLUMNS ( Sales[Store], "Store Sales", [Total Sales] ),
[Store Sales], DESC )
ORDER BY [Store Sales] DESC // Old Mill 18, Riverside 16
Filter a grouped result, then rank stores.

[192][194][196]

[195][197]

You are an analytics engineer at Juniper Coffee. You need the two stores with the highest sales, shown highest first. Which query returns exactly that?

Pick one answer.

[192][194]

TOPN picks the winners. ORDER BY lines them up. You need both. Picture: Retro pixel art, 16-bit video game style.

Test a measure before you save it: DEFINE MEASURE

Exam depth: know it cold.

DEFINE MEASURE Table[Name] = expression creates a query-scoped measure. It exists only while the query runs. If a model measure has the same name, the query uses your new formula, and the model measure is untouched.

That makes DEFINE the safe way to build or fix a measure:

  1. Write the formula in DEFINE and run it, grouped by any columns you like.
  2. When the numbers look right, save it. Above each DEFINE MEASURE, a CodeLens link offers Update model: Add new measure or Update model: Overwrite measure.
  3. Or select Update model with changes to save every defined measure at once.

DEFINE VAR stores a value or table for the rest of the query.

[191][192]

dax
DEFINE
MEASURE Sales[Avg Sale] = DIVIDE ( [Total Sales], COUNTROWS ( Sales ) )
VAR MinAmount = 10
EVALUATE
SUMMARIZECOLUMNS ( Store[Region], "Avg Sale", [Avg Sale] )
// North 7.25 (29 / 4), South 8 (16 / 2)
EVALUATE
FILTER ( Sales, Sales[Amount] > MinAmount )
// Orders 3 and 5 (Amount 12 each)
One DEFINE block serves both EVALUATE statements.

[192]

Put in order

Put the measure update workflow in DAX query view in order.

Drag, or focus an item and press Space, move with the arrow keys, then Space to drop.

  1. Select the measure in the query and choose Define
  2. Select Update model: Overwrite measure in the CodeLens
  3. Check the results grid against numbers you trust
  4. Right-click the measure and choose Quick queries > Evaluate
  5. Edit the formula in the DEFINE block and run the query

[191]

Try it in Fabric

A free Fabric trial is enough. You need a semantic model in a workspace where you have Write permission, and the workspace setting from above turned on.

  1. In the workspace, open the semantic model's More options (...) and select Write DAX queries.
  2. In the Data pane, right-click a table and select Quick queries > Show top 100 rows. The query runs on its own.
  3. Replace it with EVALUATE SUMMARIZECOLUMNS ( 'YourTable'[AnyColumn], "Rows", COUNTROWS ( 'YourTable' ) ) and select Run.
  4. Add DEFINE MEASURE 'YourTable'[Test] = COUNTROWS ( 'YourTable' ) * 2 at the top and add "Test", [Test] to the query. Run it again.
  5. Close the tab. Web queries aren't kept, and nothing reached the model until you choose Update model.

[191]

Exam-style practice

You are an analytics engineer at Juniper Coffee. This query should show each region with its own sales: EVALUATE ADDCOLUMNS ( VALUES ( Store[Region] ), "Region Sales", SUM ( Sales[Amount] ) ). Both regions show 45. What should you change?

Pick one answer.

[195][197]

You are a BI developer at Juniper Coffee. You want to try a new formula for the model measure [Total Sales] on real data. Report users must not see any change until you approve it. What should you do?

Pick one answer.

[191][192]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[191][192][193][194][195][196]

Worth keeping

  1. Every DAX query is EVALUATE plus a table; DEFINE adds query-only measures and variables.
  2. FILTER picks rows, SELECTCOLUMNS picks columns, SUMMARIZECOLUMNS groups and aggregates.
  3. TOPN chooses the rows; ORDER BY sorts them. Ties can return extra rows.
  4. Test measures with DEFINE MEASURE, then save with Update model.

Your queries run fast on six rows. On six billion, whether Power BI copied the data into memory or reads it live from the source changes everything. Next lesson: Import, DirectQuery, Direct Lake and Dual.

Leaderboard

Loading…

50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.

Sources for this lesson (8)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [191]Microsoft Learn: Work with DAX query view
  3. [192]Microsoft Learn: DAX queries
  4. [193]Microsoft Learn: SUMMARIZECOLUMNS
  5. [194]Microsoft Learn: TOPN
  6. [195]Microsoft Learn: ADDCOLUMNS
  7. [196]Microsoft Learn: CALCULATETABLE
  8. [197]Microsoft Learn: CALCULATE