4.4Optimization & performance (Performance Analyzer)
The essentials in 30 seconds
- You don't guess a slowdown, you time it: the Performance Analyzer (View) measures each visual — DAX query vs rendering.
- Slow DAX query = model or measure to review; slow rendering = overly loaded visual.
- The order of remedies: model first (star schema, useless columns removed in Power Query, reduced cardinalities, auto date/time disabled), measures next, visuals last (fewer per page).
Running mini-case — Zineb tracks down her report's slowness
Zineb, a management controller at an agri-food SME in Algiers, gets the remark: her sales report is "slow". Instead of guessing, she applies the method. She opens View → Performance Analyzer, starts recording and refreshes the visuals: one bar chart blows up the timer. She reads the detail: the time is in "DAX query" — the culprit is the model, not the visual. She goes back into Power Query to remove the columns the report doesn't use, reduces cardinalities and unchecks "Auto date/time" in Options. She re-times: the gain is clear. Her reflex is born: time it, diagnose it, lighten it — model, measures, then visuals.
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 4.4.
1. Time it first
A slow report is a dead report: beyond a few seconds, users drop off. Diagnosis starts with the Performance Analyzer (View → Performance Analyzer): you start recording, refresh the visuals, and each visual shows its detailed time. Two items are read: the DAX query (the cost of the model and the measures) and the rendering (the visual's display). Zineb no longer guesses: she reads the ranking of times.
2. Read the two items
The interpretation is simple: slow DAX query = the model or measure needs review; slow rendering = the visual is too loaded (too many points, too many categories). You can even copy the query ("Copy query") for fine analysis in DAX Studio. Zineb thus identifies her faulty bar chart and knows where to act before touching anything at random.
3. The hierarchy of remedies
The remedies follow a proven order: the model first (star schema, useless columns removed in Power Query, reduced cardinalities, auto date/time disabled), the measures next (VAR variables, targeted filters), the visuals last (fewer per page — each visual = one query). This checklist solves the vast majority of real slowdowns, and you re-time to measure the gain. Zineb applies it in that order, without skipping a step.
🔎 Concrete example — 🔎 Concrete example — The hygiene checklist
Zineb's method, in order:
1. Performance Analyzer → Start → Refresh visuals
2. The slowest: time in "DAX query" or in "Rendering"?
3. Power Query: remove the UNUSED columns (each one weighs)
4. Options: uncheck "Auto date/time"
5. Re-time: measure the gain.Reading: you act first on the model, at the source (Power Query), not on the visual.
Lesson: re-timing is part of the method — you measure the gain.
Section vocabulary
- Performance Analyzer
- View → Performance Analyzer: start recording, refresh the visuals — each visual shows its detailed time.
Simple example: Zineb times her sales report there.
What it is not: guessing the slowness by eye.
Reflex: slowness → time it first.
- DAX query vs Rendering
- The two timed items: slow query = model/measure to review; slow rendering = overly loaded visual.
Simple example: Zineb sees that her time is in the DAX query.
What it is not: a single figure without detail.
Reflex: query → model/measure; rendering → visual.
- Model hygiene
- Remove useless columns (in Power Query, not after), reduce cardinalities, disable auto date/time.
Simple example: Zineb cuts the unused columns at the source.
What it is not: importing everything "just in case".
Reflex: each imported column weighs.
- Copy query
- Each slow measure can be copied (Copy query) for analysis in DAX Studio — the practitioners' free tool.
Simple example: Zineb copies the faulty visual's query.
What it is not: rewriting the measure at random.
Reflex: fine analysis → DAX Studio.
- Too many visuals
- Each visual = one query: a page with 15 visuals sends 15 queries on every click. Frugality is an optimization.
Simple example: Zineb lightens her overloaded page.
What it is not: stacking visuals to "look rich".
Reflex: fewer visuals = fewer queries.
In practice — In practice — Time, diagnose, lighten
- View → Performance Analyzer → Start recording → Refresh visuals: read the ranking of times.
- Analyze the slowest: time in "DAX query" (review the measure/model) or in "Rendering" (simplify the visual)?
- Hygiene: in Power Query, remove the columns the report does NOT use; check in Options that "Auto date/time" is unchecked.
- Re-time after optimization: measuring the gain is part of the method.
Step-by-step script — optimizing like Zineb (7 min)
1) View → Performance Analyzer → Start recording, then Refresh visuals: each visual shows its time. 2) Spot the slowest and look at the dominant item: "DAX query" (model/measure) or "Rendering" (overly loaded visual). 3) Go back into Power Query and remove the columns the report doesn't use. 4) Options → uncheck "Auto date/time". 5) Reduce the number of visuals on the page, then re-time to measure the gain.
If the culprit is in the DAX query, copy the visual's query (Copy query) to analyze it in DAX Studio.
Copy-paste template — my performance checklist
1. Time it: View → Performance Analyzer → Start → Refresh
2. Read: DAX query (model/measure) or Rendering (visual)?
3. Model first: Power Query → remove useless columns;
reduce cardinalities; Options → uncheck "Auto date/time"
4. Measures next: VAR variables, targeted filters
5. Visuals last: fewer per page (each visual = one query)
6. Re-time: measure the gain.Time it
Zineb launches the Performance Analyzer before any optimization: the culprit is named.
Model first
Zineb cuts useless columns in Power Query and reduces cardinalities.
Auto date/time
Zineb unchecks auto date/time: no more hidden tables per date column.
Frugality
Zineb removes visuals from her page: fewer queries on every click.
✓ Best practices
- Time it before optimizing (Performance Analyzer).
- Follow the order model → measures → visuals.
- Remove useless columns in Power Query, at the source.
✗ Common pitfalls
- Guessing the slowness instead of timing it.
- Leaving auto date/time on (hidden tables, bloated file).
- Stacking visuals (each visual = one query).
Key takeaways
- You don't guess a slowdown, you time it: the Performance Analyzer first.
- Slow DAX query = model/measure; slow rendering = overly loaded visual.
- The order of remedies: model → measures → visuals.
- Each imported column weighs: remove the useless in Power Query; disable auto date/time.
48h challenge — apply it tomorrow
Like Zineb, run a report through the Performance Analyzer: identify the slowest visual and its dominant item (DAX query or rendering). Remove useless columns in Power Query, uncheck auto date/time, then re-time and note the gain.
- I can time a report and read DAX query vs rendering.
- I know the order of remedies: model → measures → visuals.
- I know how to lighten a model at the source (columns, cardinalities, auto date/time).
Frequently asked questions
My .pbix file is huge: how do I reduce it?
The usual suspects: useless columns (especially unique IDs and long texts), automatic date hierarchies (one hidden table per date column), and overly granular data (the hour when the day is enough). The free VertiPaq Analyzer tool (DAX Studio) lists the exact weight of each column.
Would DirectQuery be faster than Import?
Almost always the opposite: Import (compressed in memory) is the fastest mode. DirectQuery is justified by real-time freshness or volumes impossible to import — never by speed.
What is the right order of remedies for a slowdown?