Launch Deal · 50% off 1st month ·

3.7 — Optimize Queries, Visuals and DAX with Performance Tools

DP-600 Domain 3 · Skill 3.7

What You Need to Know

Slow reports are diagnosed from the visual down to the engine: which visual is slow, which DAX query it sends, where the engine spends its time, and what in the model causes it. Each tool covers one step.

The tools

Tool What it's for
Performance analyzer (Power BI Desktop) Times each visual: DAX query, visual display, and other. Copy the DAX query of the slow visual
DAX query view (Power BI Desktop) Run and tweak DAX queries in Desktop; define and test measures
DAX Studio (free) Run the copied query with Server Timings: storage engine (SE) time against formula engine (FE) time, the number of SE queries, and cache hits. Query plans. VertiPaq Analyzer for table and column sizes and cardinality
Tabular Editor 2 (free) Edit the model offline or through the XMLA endpoint: bulk changes, calculation groups, OLS, perspectives, scripting (C#), and the Best Practice Analyzer (BPA), which flags issues such as unused columns, bidirectional relationships, floating-point keys and missing format strings
Best Practice Analyzer in Fabric notebooks Semantic link (semantic-link-labs) runs BPA and VertiPaq-style statistics from a notebook

Reading Server Timings

  • Most time in the SE (many or large scans): reduce data (remove columns, lower cardinality), add aggregations, check the relationships.
  • Most time in the FE (single-threaded logic): rewrite the DAX, for example with fewer nested iterators over big tables, variables, simpler filters, and avoiding FILTER(ALL(FactTable), ...).

Model and DAX optimizations

Area Optimization
Size and cardinality Remove unused columns; split datetime into a date and a time; round decimals; avoid high-cardinality text columns; integer keys
Relationships Single-direction 1:* from a star schema; avoid unnecessary bidirectional or many-to-many relationships
DAX Variables instead of repeated expressions; DIVIDE; column predicates in CALCULATE rather than FILTER over tables; avoid row-by-row iterators over the whole fact table when a simple aggregation works
Calculated columns Move them upstream (in SQL or Power Query) rather than computing them in the model
Auto date/time Turn it off and use one marked Date table

Report and visual optimizations

  • Fewer visuals per page, and fewer high-cardinality table visuals. Use drill-through or tooltips for detail.
  • Apply filters and slicers that reduce the data before it's queried. Avoid "show items with no data" on big axes.
  • For DirectQuery: query reduction options (an Apply button on slicers), aggregations, and limiting cross-highlighting.
  • For Direct Lake: keep the Delta tables compacted and V-Ordered, and avoid fallback.

Key Concept: Performance analyzer finds the slow visual and its query. DAX Studio shows SE versus FE time and column sizes. Tabular Editor applies fixes and runs the Best Practice Analyzer. Shrink the model (columns, cardinality), keep it a star, and simplify the DAX.

Exam Traps

  • Using Tabular Editor to measure query time (that's DAX Studio or Performance analyzer)
  • Adding hardware (a bigger SKU) before checking column cardinality and DAX
  • Keeping a full datetime column with second-level precision as a slicer or key
  • Writing calculated columns in DAX that could come from the source

Check your understanding

Practice Scenario

🔒 This practice scenario is part of Skillmarked Pro

The lessons stay free. Pro unlocks the other 17 DP-600 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.

See plans

Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.

Cancel anytime. Or €199 a year, which saves 17%.

Not sure yet? Try 10 free mock exam questions.

Already Pro? Log in.