2.8 — Optimize Model Performance
PL-300 Domain 2 · Skill 2.8
What You Need to Know
Slow reports usually come from one of three places: a model that's too big, inefficient DAX, or too many or too heavy visuals. The exam expects you to find the cause with the right tool, then fix it.
Make the model smaller
Import models are compressed per column. Size grows with rows and, above all, with the number of distinct values in each column (cardinality).
| Technique | Example |
|---|---|
| Remove unnecessary columns | Drop audit columns, GUIDs, comments and unused descriptions in Power Query |
| Remove unnecessary rows | Filter to the years the report needs; exclude cancelled test orders |
| Reduce granularity | Group transactions to daily per product per store when nobody needs single transactions; store targets at month level |
| Lower cardinality | Split Date/Time into Date and Time; round times to the minute; round decimals |
| Better types | Integer keys instead of text keys |
| Turn off Auto date/time | Removes one hidden table per date column (lesson 2.3) |
| Prefer Power Query columns over DAX calculated columns | They compress better |
| Disable load for staging queries | Lesson 1.8 |
Reducing granularity is a trade-off: smaller and faster, but drill-down to detail is gone. Composite models with aggregations can keep detail in DirectQuery while summaries are imported.
Find the slow part: Performance Analyzer
In Power BI Desktop: Optimize > Performance analyzer > Start recording, then Refresh visuals or interact with the page. For each visual you see:
| Measure | Meaning | If it's high |
|---|---|---|
| DAX query | Time for the engine to answer the visual's query | Optimize the measure or model |
| Visual display | Time to render | Too many data points, or a heavy custom visual |
| Other | Waiting for other visuals or background work | Too many visuals on the page |
| Direct query | Time at the source (DirectQuery only) | Tune the source |
Copy query copies the DAX query, and Run in DAX query view opens it in DAX query view.
DAX query view
DAX query view (in Desktop) runs EVALUATE queries against the model. Use it to:
- Re-run a slow visual's query and test a rewritten measure with
DEFINE MEASUREbefore changing the model - Use quick queries to show the top rows of a table, column statistics, or all measures' definitions
- Ask Copilot to explain or write a query
Common fixes
- Measures: replace
FILTER(Sales, …)over a whole table with column filters in CALCULATE; use variables (VAR) so an expression isn't calculated twice; use DIVIDE. - Relationships: replace unnecessary bidirectional or many-to-many relationships with single-direction one-to-many (lesson 2.2).
- Visuals: fewer visuals per page; avoid tables showing thousands of rows; use Optimize > Optimization presets or apply-all-slicers buttons in DirectQuery reports to reduce queries.
Key Concept: Shrink the model first (fewer columns, fewer rows, lower cardinality, lower granularity), then use Performance Analyzer to see whether the DAX query or the rendering is slow, and test rewritten DAX in DAX query view.
Exam Traps
- Optimizing DAX when Performance Analyzer shows the time is in Visual display or Other
- Keeping a high-cardinality Date/Time column with seconds that's never used
- Reducing granularity when the requirement says users must see individual transactions
- Turning on bidirectional filters to speed up a slicer (usually slows the model)
- Thinking hiding a column reduces model size (it doesn't; remove it)
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 22 PL-300 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
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.
Sources
- Use Performance Analyzer to examine report element performance, Microsoft Learn
- Work with DAX query view, Microsoft Learn
- Data reduction techniques for Import modeling, Microsoft Learn
- Optimization guide for Power BI, Microsoft Learn