"It used to be fast" is the most common complaint we hear about Power BI reports. Usually nothing broke — the model just grew: more rows, more measures, more visuals on the same page, added one at a time over months. By the time someone notices, nobody remembers which change caused it.
This article is the workflow we use to find the real cause instead of guessing, and the fixes that actually move the needle.
First, find out what is actually slow
Open View → Performance Analyzer and refresh the page with recording on. It breaks every visual into three numbers:
- DAX query — time the engine (VertiPaq or DirectQuery source) spent computing the measure.
- Visual display — time the visual took to render once it had the data.
- Other — everything else: cross-filtering, waiting on other visuals, etc.
Export the result to JSON (there's a button for it) if you want to compare before/after a fix. Two details make the numbers trustworthy: record a full page load instead of clicking visuals one at a time, and do it on a page you haven't just visited — the engine caches query results, so the second run of the same visual looks artificially fast. Nine times out of ten, one or two visuals account for most of the time on the page — fix those first, don't spread effort evenly.
If the slow part is "DAX query", copy that query into DAX Studio and run it with Server Timings on. It splits the time between the Formula Engine (FE, row-by-row logic, single-threaded) and the Storage Engine (SE, scanning compressed columns, can run in parallel). A query that's mostly FE time points to the DAX itself; mostly SE time points to the data volume or model design.
The usual suspects
1. Calculated columns instead of Power Query / source columns. A calculated column is computed row by row at refresh time and materialised in the model, but it doesn't get the same treatment as an imported column: VertiPaq chooses encoding and sort order from the data it loads, so a DAX column usually compresses worse — much worse when it's high cardinality or built by concatenating text. If the same logic can live in Power Query (M) or upstream in the source, the resulting column compresses properly and the calculated column disappears entirely.
2. High cardinality columns. A DateTime column with millisecond precision, or a free-text Notes field, can bloat the model more than tables with ten times the row count. Split DateTime into Date + Time (or just Date if time isn't needed), round decimals that don't need six digits of precision, and drop text columns you don't filter or display on. While you're there, turn off File → Options and settings → Options → Current File → Data Load → Auto date/time: it builds a hidden date table for every date column in the model, and on a model with a dozen date fields that alone can be a sizeable share of the file.
3. Iterating DAX that re-scans the fact table. A measure like this:
Total Margin % =
SUMX (
Sales,
DIVIDE ( Sales[Revenue] - Sales[Cost], Sales[Revenue] )
)
walks the fact table row by row in the Formula Engine, so it can't lean on the Storage Engine's columnar aggregation. Rewrite it as:
Total Margin % =
DIVIDE ( SUM ( Sales[Revenue] ) - SUM ( Sales[Cost] ), SUM ( Sales[Revenue] ) )
Now SE does two simple sums and FE divides once. And note that this isn't only a refactor — it's a bug fix that happens to be faster. The first version adds up one percentage per row, so a million rows return a number in the thousands of percent. Slow measures and wrong measures travel together more often than you'd expect, and a ratio built inside an iterator is the most common pair.
When the per-row expression is additive, the rewrite is exactly equivalent and still worth doing: SUMX ( Sales, Sales[Revenue] - Sales[Cost] ) returns the same value as SUM ( Sales[Revenue] ) - SUM ( Sales[Cost] ), but the second form is answered by the Storage Engine alone. Keep the iterator only when the row-level math genuinely can't be expressed as sums — SUMX ( Sales, Sales[Qty] * Sales[UnitPrice] ), where the product has to be evaluated per row before anything is added up.
4. Too many visuals on one page. Every visual issues its own DAX query. Twenty cards and slicers on a single page mean twenty-plus queries fire on load and on every filter change. Split dense pages into tabs, use bookmarks to swap content instead of stacking everything, and prune cross-filtering with Format → Edit interactions, setting the visuals that don't need to react to a given slicer to "none" — fewer interactions firing means fewer queries per click. While you're building, Optimize ribbon → Pause visuals lets you lay out a dense page without every change triggering a new round of queries.
5. DirectQuery where Import would do. DirectQuery sends a query to the source for every interaction — it inherits the source's performance and skips VertiPaq compression entirely. If the data doesn't need to be real-time, Import mode is almost always faster, because the engine queries an in-memory columnar copy instead of round-tripping to SQL Server or a warehouse. If DirectQuery is genuinely required (compliance, a fact table too big to import), make sure the source has the right indexes, keep the model free of DAX that forces row-by-row evaluation, and consider a composite model with aggregations (below).
6. A snowflake model instead of a star schema. Chained lookup tables (dimension → sub-dimension → sub-sub-dimension) force the engine to walk multiple relationships for a single filter. Flatten dimensions into one wide table per entity (a single Product table with category, subcategory and brand as columns, not three linked tables) so each fact table has one relationship per dimension.
Fixes that scale: aggregations and incremental refresh
For fact tables in the tens of millions of rows, two techniques consistently help:
- Aggregation tables. Build a pre-summarized table (e.g., Sales by Date/Product/Store, one row per combination, instead of one row per transaction) and register it in Manage aggregations so it's used automatically whenever a visual's filters allow it. Power BI silently swaps the detailed table for the aggregated one when it can, and falls back to detail only when someone drills to transaction level. The dramatic version is the composite model — detail left in DirectQuery, aggregation imported — where the fast path never touches the source at all.
- Incremental refresh. If the fact table has a date column and most history doesn't change, configure incremental refresh so only the recent partitions are reprocessed instead of the whole table. In Power BI Desktop it's two steps: create the
RangeStartandRangeEnddate/time parameters in Power Query and filter the table by them, then right-click the table in the Data pane → Incremental refresh and set how much history to store and how much to refresh. This cuts refresh time, not interactive query time, but a refresh that times out is just as much a performance problem.
A short checklist before you ship
- Run Performance Analyzer on every page with real-world filters applied (not an empty report), on a cold cache.
- Remove calculated columns you can replace with Power Query or source-side logic.
- Rewrite iterating measures (
SUMX,AVERAGEX) that could be a division of sums instead. - Check cardinality on your biggest tables — datetime and free text are the usual offenders — and confirm Auto date/time is off.
- Confirm the model is a star schema: one fact table, flat dimensions, one relationship each.
- Cap visuals per page, split dense pages into tabs or drillthroughs.
- For big fact tables, test whether aggregations change the story — often a 50M-row table behaves like a 500K-row one for 90% of the visuals.
None of this requires rebuilding the report from scratch. Most of the time, the biggest win comes from the two or three visuals Performance Analyzer flags first — fix those, measure again, and decide whether the rest is worth the effort.
Got a Power BI report that used to load in two seconds and now takes twenty? This kind of diagnosis is our day job — see how we work on our Power BI consulting page.