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)
22 min
Faster reports and DAX: finding and fixing slow queries
Find slow visuals and DAX with Performance analyzer and DAX query view, and fix them.
By the end of this lesson you can
- Use Performance analyzer to find the slow visual and the slow part of it
- Pick report fixes: fewer visuals, tighter filters, query reduction, aggregations
- Pick model fixes that shrink memory: fewer columns, lower cardinality, no auto date/time
- Spot slow DAX patterns and choose the faster rewrite
From earlier lessons · 1 of 2
During development of a Direct Lake on SQL analytics endpoint model, you want any query that would fall back to DirectQuery to fail with an error. Which Direct Lake behavior setting should you pick?
Pick one answer.
From earlier lessons · 2 of 2
Which model property must be on before Power BI lets you add a calculation group?
Pick one answer.
Juniper Coffee's store managers open the Store performance page each morning. It has 14 visuals and takes 12 seconds to load. One manager says, "I just look at last week's sales, why is this so slow?" You need to find which visual is slow, and why, before you change anything.
Performance analyzer: which visual, which part
Exam depth: know it cold.
Performance analyzer is a pane that times every visual as it loads. In Power BI Desktop, open the Optimize ribbon and select Performance analyzer. In the web, edit the report and use View > Performance analyzer.
Select Start recording, then Refresh visuals to time the whole page. Each visual splits into parts:
| Row | Time spent |
|---|---|
| DAX query. | The model answering the visual's DAX query. |
| Direct query. | The source answering, for DirectQuery tables. |
| Visual display. | Drawing the visual on screen. |
| Other. | Preparing queries and waiting for other visuals. |
| Evaluated parameters. | Evaluating field parameters (preview). |
Read the biggest number first. It tells you where to look.
- DAX query is large: the measure or the model is slow. Select Copy query, or Run in DAX query view in Desktop, and work on it there.
- Visual display is large: the visual draws too much, such as thousands of points or a slow custom visual.
- Other is large: the visual waited in line. Durations include queue time, so too many visuals on one page show up here.
Each slicer click or filter change is logged as its own interaction. Export saves the log as a .json file.
Match
Match each Performance analyzer finding to the first thing to try.
Pick an item on the left, then its match on the right.
DAX query view and the tools beyond it
Exam depth: know it cold for DAX query view, recognize it for the rest.
DAX query view runs the visual's query and shows its result grid. Rewrite a measure in a DEFINE MEASURE block and run it again. When the result matches, use Update model with changes to save it. Then record the page again in Performance analyzer and compare times. In the web, Write DAX queries does the same but needs Write permission on the model.
External tools go deeper. DAX Studio, a free open-source tool, and SQL Server Management Studio run DAX queries too. DAX Studio can capture server timings (where the engine spent its time) and query plans (the steps it took). SQL Server Profiler traces show DirectQuery SQL and whether aggregations were hit.
You are an analytics engineer at Juniper Coffee. Performance analyzer shows the Store ranking table at DAX query 8,900 ms, Visual display 70 ms, Other 40 ms. Other visuals load in under 300 ms. What should you do first?
Pick one answer.
Report and visual fixes
Exam depth: know it cold.
- Fewer visuals per page. Each visual sends its own query. Move detail to drillthrough pages and report page tooltips.
- Tighter filters. A table showing every row is expensive. A Top N filter keeps the rows readers need.
- Query reduction. On the Optimize ribbon, Optimization presets > Query reduction turns off default cross-highlighting and cross-filtering. It also adds Apply buttons, so slicer picks don't fire a query each time. Customize opens the same settings in Options.
- Apply all slicers and Clear all slicers buttons cut the queries a page of slicers sends.
- Slicers on columns with few distinct values. A slicer over every loyalty member ID is a slow visual on a high-cardinality column.
You are a BI developer at Juniper Coffee. A DirectQuery report over the till database has five slicers. Managers pick several values in each, and every pick sends queries to the source. The slicers must stay. What should you do?
Pick one answer.
Model fixes: a smaller model is a faster model
Exam depth: know it cold.
Cardinality is the number of distinct values in a column. Fewer distinct values compress better and scan faster.
| Fix | Why it helps |
|---|---|
| Remove columns no visual, relationship or calculation uses. | Less memory per row. |
| Load fewer rows, such as the last three years. | Smaller tables, faster refresh. |
| Group by and summarize, such as daily totals per store. | Far fewer rows, but you lose detail. |
| Numbers instead of text, such as SO123456 as 123456. | Numbers use value encoding; text needs a hash lookup. |
| Lower cardinality, such as a date instead of a timestamp. | Fewer distinct values. |
| New columns in Power Query, not DAX calculated columns. | Better compression and faster refresh. |
| Disable load for staging queries. | They never reach the model. |
Exam depth: recognize it.
Two ready-made Fabric notebooks check a published semantic model. Open them from the model's More options (...) in the OneLake catalog, or from Model health on its details page.
- Best Practice Analyzer (BPA) checks more than 60 rules in five groups: Performance, DAX Expressions, Error Prevention, Maintenance and Formatting.
- Memory Analyzer shows how much memory each table, column, partition and relationship uses. It points you at the column to shrink first.
They need Fabric capacity, Build permission on the model, and Contributor on the notebook's workspace. Tabular Editor, an external tool, has its own Best Practice Analyzer.
You are an analytics engineer at Juniper Coffee. Memory Analyzer shows two large Sales columns: ReceiptText, which nothing uses, and SaleTimestamp, unique to the second. Reports only group by day. You need the smallest model. What should you do?
Pick one answer.
DAX fixes: patterns to rewrite
Exam depth: know it cold.
| Slow pattern | Faster rewrite | Why |
|---|---|---|
| The same expression written twice. | Store it once in a VAR. | The engine evaluates it once. |
CALCULATE([Sales], FILTER(Sales, Sales[Channel] = "App")). | CALCULATE([Sales], KEEPFILTERS(Sales[Channel] = "App")). | Column filters are what the column store is built for. |
IFERROR([Profit] / [Sales], BLANK()). | DIVIDE([Profit], [Sales]). | Error functions add storage engine scans. |
| An iterator inside an iterator over big tables. | Iterate a small table, such as VALUES of a column. | The inner loop runs once per outer row. |
| A calculated column used only for totals. | A measure, or a Power Query column. | Calculated columns use memory and slow refresh. |
KEEPFILTERS keeps any filter already on that column, as FILTER over the visible rows did.
-- Before: the prior-year CALCULATE runs twice, with FILTER over a tableLatte YoY % =DIVIDE (CALCULATE ( [Revenue], FILTER ( 'Product', 'Product'[Product] = "Latte" ) )- CALCULATE ( [Revenue], FILTER ( 'Product', 'Product'[Product] = "Latte" ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ),CALCULATE ( [Revenue], FILTER ( 'Product', 'Product'[Product] = "Latte" ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) )-- AfterLatte YoY % =VAR Cur = CALCULATE ( [Revenue], KEEPFILTERS ( 'Product'[Product] = "Latte" ) )VAR Prev = CALCULATE ( [Revenue], KEEPFILTERS ( 'Product'[Product] = "Latte" ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )RETURN DIVIDE ( Cur - Prev, Prev )
You are an analytics engineer at Juniper Coffee. Margin % = IFERROR([Profit] / [Revenue], BLANK()) is slow on a large model. It must still return BLANK when revenue is zero. Which rewrite is recommended?
Pick one answer.
Direct Lake and DirectQuery: the extra checks
Exam depth: recognize it. Lesson 24 covers fallback in full.
- Fallback is slower. Direct Lake on SQL analytics endpoint can fall back to DirectQuery, for example on a SQL view or SQL-level security. A visual that fell back shows a Direct query row in Performance analyzer.
EVALUATE TABLETRAITS()names the reason per table. Direct Lake on OneLake never falls back. - Healthy Delta tables load faster. Apply V-Order, keep the number of Parquet files small and use large row groups.
OPTIMIZEandVACUUMhelp a table stay inside guardrails. - DirectQuery: use query reduction, limit visuals per page, and avoid bidirectional filters.
Try it in Fabric
Use any report in Power BI Desktop, or edit one in the Fabric portal.
- On the Optimize ribbon, select Performance analyzer. In the web, use View > Performance analyzer.
- Select Start recording, then Refresh visuals.
- Expand the slowest visual. Compare DAX query, Visual display and Other.
- In Desktop, select Run in DAX query view. In the web, select Copy query and paste it into DAX query view.
- Select Run and check the result grid matches the visual.
- Back in the pane, select Stop, then Export to save the log.
Exam-style practice
You are an analytics engineer at Juniper Coffee. A Direct Lake on SQL analytics endpoint model is fast, except one visual. It shows a Direct query row in Performance analyzer. Its table is a SQL view in the Warehouse. You need Direct Lake speed for it. What should you do?
Pick one answer.
You are an analytics engineer at Juniper Coffee. App Revenue = CALCULATE([Revenue], FILTER(Sales, Sales[Channel] = "App")) is slow on 80 million till lines. You need the same result faster. Which measure should you use?
Pick one answer.
Worth keeping
- Measure first: Performance analyzer shows which visual and which part is slow.
- Big DAX query: tune the measure or model. Big Other: too many visuals on the page.
- Shrink the model: drop unused columns and rows, cut cardinality, turn off auto date/time.
- Rewrite slow DAX: VAR once, column filters over FILTER(table), DIVIDE over IFERROR.
- Direct Lake: avoid fallback and keep Delta tables V-Ordered and compact.
The Store performance page now loads in two seconds. But who should be allowed to open it, edit it, or build new reports on its model? Next lesson: workspace roles and item permissions.
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 (24)
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
- [57]Microsoft Learn: DAX overview
- [63]Microsoft Learn: Create calculation groups
- [191]Microsoft Learn: Work with DAX query view
- [198]Microsoft Learn: How Direct Lake works
- [206]Microsoft Learn: Bi-directional relationship guidance
- [213]Microsoft Learn: Use variables to improve your DAX formulas
- [217]Microsoft Learn: Avoid using FILTER as a filter argument
- [236]Microsoft Learn: Appropriate use of error functions
- [242]Microsoft Learn: User-defined aggregations
- [243]Microsoft Learn: Composite model guidance in Power BI Desktop
- [248]Microsoft Learn: Understand Direct Lake query performance
- [252]Microsoft Learn: Use Performance Analyzer to examine report performance
- [253]Microsoft Learn: Optimization guide for Power BI
- [254]Microsoft Learn: Optimize ribbon in Power BI Desktop
- [255]Microsoft Learn: DirectQuery optimization scenarios with the Optimize ribbon
- [256]Microsoft Learn: Create Apply all and Clear all slicers buttons in Power BI reports
- [257]Microsoft Learn: Data reduction techniques for Import modeling
- [258]Microsoft Learn: Auto date/time guidance in Power BI Desktop
- [259]Microsoft Learn: Apply auto date/time in Power BI Desktop
- [260]Microsoft Learn: Use notebooks with a semantic model
- [261]Microsoft Learn: Use tools to optimize Power BI performance
- [262]Microsoft Learn: DirectQuery in Power BI
- [263]Microsoft Learn: Analyze query processing for Direct Lake semantic models