Optimize the whole path: source → model → DAX → visuals → user experience.
Complete 12 of 24 practices (50%) and enter your name to unlock the Certificate of Participation.
Performance Analyzer helps identify which visuals are slow and where their time is being spent. It turns 'the report feels slow' into measurable evidence.
If one matrix takes 4.5 seconds while every card renders in 80 milliseconds, rebuilding the whole page is less useful than isolating that matrix first.
1. Open Performance Analyzer
2. Start recording
3. Refresh visuals
4. Reproduce the slow interaction
5. Sort by duration
6. Identify the slowest visual
7. Copy query if deeper DAX analysis is needed
8. Save the baseline before changesThe first optimization deliverable is not a fix; it is a trustworthy baseline.
Import models benefit from efficient columnar compression. Unused columns, unnecessary detail and high-cardinality attributes can make the model heavier than the business question requires.
A transaction GUID used only for debugging may have millions of unique values. If report users never need it, carrying it into the semantic model can cost memory without analytical benefit.
Ask for every column:
- Is it used in a visual?
- Is it used in a relationship?
- Is it used in DAX?
- Is it needed for RLS?
- Is it needed for drillthrough/detail?
If NO to all → candidate for removal.
Ask for every fact row:
- Is this grain required by the report?Model optimization starts with business necessity: keep what the report needs, not everything the source happens to contain.
Good DAX performance begins with a good model, but formula design still matters. Reuse base measures, reduce repeated expressions, understand filter context, and avoid creating stored calculated columns when a dynamic measure is the better fit.
Instead of repeating SUM(FactSales[Amount]) in ten formulas, create [Sales] once and build [Sales YTD], [Sales LY] and [Sales Margin] on top of it.
Sales =
SUM(FactSales[Amount])
Cost =
SUM(FactSales[Cost])
Margin =
VAR Revenue = [Sales]
VAR Expense = [Cost]
RETURN
Revenue - Expense
Margin % =
DIVIDE([Margin], [Sales])Optimize DAX after the model is sound and after measurement shows that DAX is actually the slow layer.
Every visual has a cost. On interactive pages, slicers, cross-highlighting and cross-filtering can generate repeated queries. Report design is therefore part of performance engineering.
If ten slicers each refresh on every click in DirectQuery, the source can receive a flood of queries before the user finishes selecting the intended filters.
For a heavy DirectQuery page:
- Apply filters before adding expensive visuals
- Consider Apply all slicers
- Reduce unnecessary cross-highlighting
- Pause / refresh visuals during authoring when useful
- Use Performance Analyzer after each major design changeVisual performance is a design decision: every chart, slicer and interaction should justify the query work it creates.
Storage mode determines where query work happens. Import often gives the fastest interactive experience because data is in the semantic model. DirectQuery keeps queries closer to the source but makes source/network efficiency much more important. Composite models combine modes when needed.
A model that needs near-real-time detail may accept DirectQuery complexity, while a daily executive dashboard may benefit more from Import and scheduled refresh.
Ask:
- How fresh must data be?
- How large is the model?
- Can the source handle interactive concurrency?
- Is network latency acceptable?
- Can high-level aggregates be imported?
- Is detailed live access required?
- What refresh window is available?Storage mode is a performance and freshness contract with the business.
Power Query can often fold transformations back to a relational source. When folding works, the source performs filtering, joins and projections instead of moving unnecessary raw data into the mashup engine.
Filtering 100 million rows down to 2 million at the database is usually stronger than importing all 100 million into Power Query just to remove 98 million later.
Prefer:
Source view / foldable Power Query
→ filtered, typed, shaped data
→ semantic model
Avoid:
Huge raw extract
→ many non-folding transformations
→ late filters
→ oversized modelPerformance improves when each layer does the work it is best suited to do.
Relationships determine how filter context travels. Complex many-to-many designs and unnecessary bidirectional filtering can make query behavior harder to predict and can increase query complexity.
If filters can reach a fact table through multiple competing paths, you have both a correctness problem and potentially a performance problem.
For each relationship ask:
- Is the dimension key unique?
- Does the fact key match valid dimension values?
- Is cardinality correct?
- Is cross-filter direction necessary?
- Is there another path between the same areas?
- Could a bridge table model this more clearly?Relationship tuning protects both performance and trust in totals.
Power BI performance is end-to-end. A good production process measures source, model, DAX, visuals and interactions as one path, then assigns each bottleneck to the right layer and owner.
A performance change that cuts a visual from 4 seconds to 1 second but breaks filter logic is a regression, not an optimization.
1. Capture baseline with Performance Analyzer
2. Identify slow visual / interaction
3. Determine source vs model vs DAX vs rendering
4. Remove unnecessary data/model weight
5. Review relationships and cardinality
6. Simplify / reuse DAX where evidence points there
7. Reduce unnecessary visual/query activity
8. Validate Power Query folding / source efficiency
9. Reconsider storage mode if architecture is mismatched
10. Re-measure under comparable conditions
11. Verify totals / filters / refresh
12. Monitor after publishingPerformance optimization is not a bag of tricks. It is a disciplined measurement-and-validation loop.
Open each item after answering it in your own words. The 24 interactive practices above drive certificate progress.
Measure before changing anything.
A smaller purposeful model is easier to query efficiently.
It lets the source do more of the work.
DirectQuery keeps source/query latency in the interactive path.
Faster and still correct is the goal.
| Symptom | Investigate |
|---|---|
| One visual is slow | Performance Analyzer + DAX/query behavior |
| Whole Import model is heavy | Columns, rows, cardinality, calculated columns |
| DirectQuery page sends many queries | Visual count, interactions, query reduction, source performance |
| Refresh / load is slow | Power Query folding and source transformations |
| Totals behave strangely and queries are complex | Relationships, cardinality, filter direction |
Complete at least 12 of the 24 practice cases (50%) and enter your name.
This training is aligned to current Microsoft Learn performance guidance.
Microsoft Learn — Performance Analyzer