All 35 lessons
Start here: cloud, data, Fabric and Power BI basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. Power BI in plain words: semantic models, reports and DAX
Prepare data: get data in (exam area 2, 45-50%)
- 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
- 7. Choosing a data store: lakehouse, warehouse or eventhouse
- 8. Use data without copying: shortcuts, mirroring and OneLake integration
- 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
- 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
- 11. Messy data: duplicates, missing values and nulls
Prepare data: transform and query (exam area 2)
Design and build semantic models (exam area 3, 25-30%)
- 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
- 19. Relationships: star schemas, bridge tables and many-to-many
- 20. DAX foundations: measures, filter context, CALCULATE and variables
- 21. DAX functions the exam tests: iterators, table filters, windows and information functions
- 22. Calculation groups, dynamic format strings and field parameters
- 23. Composite models and large semantic model storage format
Optimize models, then secure them (exam areas 3 and 1)
- 24. Direct Lake in depth: fallback, refresh and the two flavors
- 25. Incremental refresh for semantic models
- 26. Faster reports and DAX: finding and fixing slow queries
- 27. Who can see what: workspace roles and item permissions
- 28. Fine-grained security: rows, columns, objects and files
- 29. Governance: sensitivity labels and endorsement
Lifecycle, then exam practice (exam area 1)
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.
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 Desktop | Power 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.
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.
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.
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 useROW(). 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.
EVALUATE Store // 3 rows: Harbour, Old Mill, RiversideEVALUATE { [Total Sales] } // 1 row: 45EVALUATE ROW ( "Orders", COUNTROWS ( Sales ) ) // 1 row, column Orders: 6EVALUATE Sales ORDER BY Sales[Amount] DESC // 6 rows, largest first
Select and filter rows: SELECTCOLUMNS and FILTER
Exam depth: know it cold.
If you know SQL, map each idea across.
| You want | SQL | DAX |
|---|---|---|
| All rows. | SELECT * FROM Sales | EVALUATE Sales |
| Some columns, renamed. | SELECT OrderID AS [Order] | SELECTCOLUMNS ( Sales, "Order", Sales[OrderID] ) |
| Filter rows. | WHERE Amount >= 8 | FILTER ( Sales, Sales[Amount] >= 8 ) |
| Distinct values. | SELECT DISTINCT Store | DISTINCT ( 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.
EVALUATEFILTER ( Sales, Sales[Product] = "Muffin" && Sales[Qty] >= 2 )// Orders 5 (Riverside, Qty 4) and 6 (Old Mill, Qty 2)EVALUATESELECTCOLUMNS ( Sales, "Order", Sales[OrderID], "Amount", Sales[Amount] )// 6 rows, 2 columns: Order and Amount
Group and aggregate: SUMMARIZECOLUMNS
Exam depth: know it cold.
SUMMARIZECOLUMNS is DAX's GROUP BY. It takes, in order:
- One or more group-by columns, written Table[Column].
- Optional filter tables that narrow the data.
- 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.
EVALUATESUMMARIZECOLUMNS (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
Predict, then reveal
Predict the result: EVALUATE FILTER ( SUMMARIZECOLUMNS ( Sales[Store], "Store Sales", [Total Sales] ), [Store Sales] > 15 )
CALCULATETABLE, ADDCOLUMNS and TOPN
Exam depth: know it cold.
| Function | What it does | Juniper 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.
EVALUATECALCULATETABLE (SUMMARIZECOLUMNS ( Store[Region], "Latte Sales", [Total Sales] ),Sales[Product] = "Latte") // North 20, South 4EVALUATETOPN ( 2, SUMMARIZECOLUMNS ( Sales[Store], "Store Sales", [Total Sales] ),[Store Sales], DESC )ORDER BY [Store Sales] DESC // Old Mill 18, Riverside 16
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.
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:
- Write the formula in DEFINE and run it, grouped by any columns you like.
- 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.
- 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.
DEFINEMEASURE Sales[Avg Sale] = DIVIDE ( [Total Sales], COUNTROWS ( Sales ) )VAR MinAmount = 10EVALUATESUMMARIZECOLUMNS ( Store[Region], "Avg Sale", [Avg Sale] )// North 7.25 (29 / 4), South 8 (16 / 2)EVALUATEFILTER ( Sales, Sales[Amount] > MinAmount )// Orders 3 and 5 (Amount 12 each)
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.
- Select the measure in the query and choose Define
- Select Update model: Overwrite measure in the CodeLens
- Check the results grid against numbers you trust
- Right-click the measure and choose Quick queries > Evaluate
- Edit the formula in the DEFINE block and run the query
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.
- In the workspace, open the semantic model's More options (...) and select Write DAX queries.
- In the Data pane, right-click a table and select Quick queries > Show top 100 rows. The query runs on its own.
- Replace it with
EVALUATE SUMMARIZECOLUMNS ( 'YourTable'[AnyColumn], "Rows", COUNTROWS ( 'YourTable' ) )and select Run. - Add
DEFINE MEASURE 'YourTable'[Test] = COUNTROWS ( 'YourTable' ) * 2at the top and add"Test", [Test]to the query. Run it again. - Close the tab. Web queries aren't kept, and nothing reached the model until you choose Update model.
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.
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.
Worth keeping
- Every DAX query is EVALUATE plus a table; DEFINE adds query-only measures and variables.
- FILTER picks rows, SELECTCOLUMNS picks columns, SUMMARIZECOLUMNS groups and aggregates.
- TOPN chooses the rows; ORDER BY sorts them. Ties can return extra rows.
- 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.
- [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
- [191]Microsoft Learn: Work with DAX query view
- [192]Microsoft Learn: DAX queries
- [193]Microsoft Learn: SUMMARIZECOLUMNS
- [194]Microsoft Learn: TOPN
- [195]Microsoft Learn: ADDCOLUMNS
- [196]Microsoft Learn: CALCULATETABLE
- [197]Microsoft Learn: CALCULATE