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

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.

[6]

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:

RowTime 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).

[252][254]

Performance analyzer pane after Refresh visuals. A Card visual is expanded into DAX query 431 ms, Visual display 25 ms and Other 181 ms, with a Copy query link.
Microsoft Learn, CC BY 4.0

[263]

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.

[252]

Match

Match each Performance analyzer finding to the first thing to try.

Pick an item on the left, then its match on the right.

[252][253]

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.

[57][191][242][252][261]

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.

[252]

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.

[248][253][254][255][256]

[242][243]

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.

[254][255]

Performance analyzer names the guilty visual and how long it took. Picture: Movie-poster parody, bold illustrated style: a cartoon barista detective in a trench coat holds a magnifying glass over a report page.

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.

FixWhy 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.

[248][257]

[206][258][259]

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.

[260][261]

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.

[257][260]

DAX fixes: patterns to rewrite

Exam depth: know it cold.

Slow patternFaster rewriteWhy
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.

[57][213][217][236][257]

dax
-- Before: the prior-year CALCULATE runs twice, with FILTER over a table
Latte 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] ) ) )
-- After
Latte 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 )
Same result, less work: one VAR per value and column filters.

[213][217]

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.

[236]

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. OPTIMIZE and VACUUM help a table stay inside guardrails.
  • DirectQuery: use query reduction, limit visuals per page, and avoid bidirectional filters.

[198][248][262][263]

Performance analyzer pane with two timed Card visuals. The first shows DAX query, Visual display and Other. The second, built on a SQL view, adds a Direct query row of 8,660 ms out of 9,205 ms.
Microsoft Learn, CC BY 4.0

[263]

Try it in Fabric

Use any report in Power BI Desktop, or edit one in the Fabric portal.

  1. On the Optimize ribbon, select Performance analyzer. In the web, use View > Performance analyzer.
  2. Select Start recording, then Refresh visuals.
  3. Expand the slowest visual. Compare DAX query, Visual display and Other.
  4. In Desktop, select Run in DAX query view. In the web, select Copy query and paste it into DAX query view.
  5. Select Run and check the result grid matches the visual.
  6. Back in the pane, select Stop, then Export to save the log.

[191][252]

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.

[198][263]

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.

[217]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[198][213][236][252][254][257][258][260]

Worth keeping

  1. Measure first: Performance analyzer shows which visual and which part is slow.
  2. Big DAX query: tune the measure or model. Big Other: too many visuals on the page.
  3. Shrink the model: drop unused columns and rows, cut cardinality, turn off auto date/time.
  4. Rewrite slow DAX: VAR once, column filters over FILTER(table), DIVIDE over IFERROR.
  5. Direct Lake: avoid fallback and keep Delta tables V-Ordered and compact.

[257]

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.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [57]Microsoft Learn: DAX overview
  3. [63]Microsoft Learn: Create calculation groups
  4. [191]Microsoft Learn: Work with DAX query view
  5. [198]Microsoft Learn: How Direct Lake works
  6. [206]Microsoft Learn: Bi-directional relationship guidance
  7. [213]Microsoft Learn: Use variables to improve your DAX formulas
  8. [217]Microsoft Learn: Avoid using FILTER as a filter argument
  9. [236]Microsoft Learn: Appropriate use of error functions
  10. [242]Microsoft Learn: User-defined aggregations
  11. [243]Microsoft Learn: Composite model guidance in Power BI Desktop
  12. [248]Microsoft Learn: Understand Direct Lake query performance
  13. [252]Microsoft Learn: Use Performance Analyzer to examine report performance
  14. [253]Microsoft Learn: Optimization guide for Power BI
  15. [254]Microsoft Learn: Optimize ribbon in Power BI Desktop
  16. [255]Microsoft Learn: DirectQuery optimization scenarios with the Optimize ribbon
  17. [256]Microsoft Learn: Create Apply all and Clear all slicers buttons in Power BI reports
  18. [257]Microsoft Learn: Data reduction techniques for Import modeling
  19. [258]Microsoft Learn: Auto date/time guidance in Power BI Desktop
  20. [259]Microsoft Learn: Apply auto date/time in Power BI Desktop
  21. [260]Microsoft Learn: Use notebooks with a semantic model
  22. [261]Microsoft Learn: Use tools to optimize Power BI performance
  23. [262]Microsoft Learn: DirectQuery in Power BI
  24. [263]Microsoft Learn: Analyze query processing for Direct Lake semantic models