A report that takes eleven seconds to load a page doesn't get used. People wait once, wait again on the next filter, and quietly go back to asking someone in finance to send them a spreadsheet.
The usual explanation — "there's too much data" — is almost never the real one. Power BI's engine is built to scan hundreds of millions of rows and will do it in under a second when the model lets it. Slowness is rarely about volume. It's about shape.
Almost every slow report we've been handed has had its problem in one of three places: the model, the DAX, or the page. They produce different symptoms, so you can usually tell which one you're dealing with before opening anything.
How to tell which one you have
Open Performance Analyzer in Power BI Desktop — View → Performance Analyzer → Start recording, then refresh the page. Each visual reports three numbers:
- DAX query — time the engine spent getting the data
- Visual display — time spent rendering it
- Other — everything else, mostly waiting
Then read the pattern:
| What you see | Where the problem is |
|---|---|
| One visual with a long DAX query time | That measure — the DAX |
| Every visual slow, roughly evenly | The model |
| DAX times fine, total still slow | The page |
This takes two minutes and saves hours of guessing. Most people skip it and start rewriting DAX at random.
The model is the wrong shape
Power BI's storage engine, VertiPaq, is a columnar store. It compresses each column independently and scans them in parallel. Two things decide how well that works: how many distinct values a column holds, and how the tables relate to each other.
Wide flat tables
Exporting one enormous table out of a source system and loading it as-is is the most common mistake we see. It feels simpler. It's slower, bigger, and harder to write against — every repeated text value gets stored in a table where it repeats thousands of times rather than once in a dimension.
The fix is a star schema: narrow fact tables holding keys and numbers, surrounded by dimension tables holding the descriptive columns. This isn't a stylistic preference. It's the shape the engine is optimised for, and the performance gap between a flat table and a proper star on the same data is routinely large.
High-cardinality columns you don't need
Compression works best when a column has few distinct values. A column with millions of unique entries — a transaction GUID, a free-text note, a timestamp down to the millisecond — compresses badly and takes real memory.
Two things to check:
Split your datetimes. A DateTime column with second-level precision has roughly one distinct value per row. Split it into a Date column and a Time column and cardinality collapses. If you don't need the time at all, drop it.
Drop unused keys. Primary keys from source systems that aren't used in a relationship or a measure cost memory and buy nothing.
Auto date/time
By default, Power BI silently creates a hidden date table for every date column in your model. Ten date columns means ten hidden tables.
Turn it off — File → Options → Data Load → Time intelligence — then build one date dimension and mark it as your date table.
Bi-directional relationships
Convenient, and they introduce ambiguity the engine has to resolve at query time. In a model with several fact tables they can create genuinely ambiguous filter paths. Use single-direction relationships and handle the exceptions explicitly in DAX with CROSSFILTER where you actually need it.
Calculated columns
A calculated column is computed at refresh and stored in the model, taking memory and compressing worse than imported data. A measure is computed at query time and stores nothing.
Default to measures. Use a calculated column when you need to group, slice, or relate on the value — not to hold a number you're only going to aggregate.
The DAX forces the slow engine
This is the one most people get wrong, and it's worth understanding the mechanism rather than memorising rules.
Power BI has two engines. The storage engine is the fast one: multi-threaded, operates on compressed columns, handles simple aggregations and filters. The formula engine is the flexible one: single-threaded, works row by row, and handles anything the storage engine can't.
Fast DAX is DAX the storage engine can answer by itself. Slow DAX is DAX that falls back to the formula engine, which then materialises intermediate results in uncompressed memory and walks them one row at a time.
Iterators over fact tables
SUMX, AVERAGEX and friends evaluate an expression per row. Over a dimension table with 400 rows, that's free. Over a fact table with 40 million, it often isn't — particularly if the expression inside triggers context transition, which forces a filter context rebuild on every single row.
Frequently the iterator isn't needed at all:
-- Forces row-by-row evaluation
Revenue = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )
-- If the multiplication can live in the source or a calculated
-- column, this becomes a single storage-engine scan
Revenue = SUM ( Sales[LineTotal] )The real decision is where the arithmetic belongs. Row-level arithmetic that never changes belongs upstream — in the warehouse, in the view, in Power Query. Pushing it into a measure means recomputing it on every query, forever.
FILTER where a predicate would do
-- FILTER returns a table the formula engine then iterates
Big Sales = CALCULATE ( SUM ( Sales[Amount] ), FILTER ( Sales, Sales[Amount] > 1000 ) )
-- A predicate the storage engine can apply directly
Big Sales = CALCULATE ( SUM ( Sales[Amount] ), Sales[Amount] > 1000 )FILTER is right when the condition genuinely needs a measure or row context. It's the wrong default.
Nested CALCULATE with overlapping filters
Each layer modifies filter context. Several layers deep, with time intelligence inside, and you're asking the engine to rebuild context repeatedly for one number.
Flatten where you can, and use variables to compute once:
Margin % =
VAR _Revenue = [Revenue]
VAR _Cost = [Cost]
RETURN
DIVIDE ( _Revenue - _Cost, _Revenue )Variables are evaluated once and reused. Referencing [Revenue] three times in one measure evaluates it three times.
Verify, don't guess
DAX Studio — free — shows you the storage engine / formula engine split for any query. If the formula engine is doing most of the work, you've found your problem.
Guessing at DAX optimisation without this is how afternoons disappear.
The page is doing too much
Every visual on a page fires at least one query. Some fire several. A page with twenty-five visuals, four slicers and three header cards is issuing thirty-plus concurrent queries every time someone changes a filter — and the page isn't finished until the slowest one returns.
This is the cause people resist most, because the fix is editorial rather than technical: the page has too much on it.
One page, one question. Every report we build follows this rule. Not one page per department, not one page per data source — one question a leadership team actually asks, and nothing on the page that doesn't help answer it. It's a design rule that happens to also be a performance rule.
Cap your visuals. Beyond roughly fifteen, load time degrades noticeably and comprehension degrades faster. If a page needs twenty visuals, it's two pages.
Replace slicers with filters where you can. Slicers query the model to populate their own lists. A filter pane item doesn't render until it's opened.
Turn off interactions you don't need. By default every visual cross-filters every other visual. Edit interactions and switch off the ones nobody uses — each one is a query that doesn't have to run.
Avoid custom visuals on the critical path. Many are fine. Some render far slower than native visuals, and you won't know which until you measure.
If you're on DirectQuery
Everything above still applies, plus one thing: each visual becomes a live query against the source system, so your report is now exactly as fast as that database under concurrent load.
If DirectQuery is a real requirement — genuine near-real-time needs, or a dataset too large to import — build aggregation tables so common queries resolve against a small imported summary and only drill-through hits the source. Without aggregations, a DirectQuery report over a busy transactional database will be slow, and no amount of DAX tuning fixes it.
It's also worth checking whether the requirement is real. "Real-time" frequently turns out to mean "I don't want yesterday's numbers," which an hourly refresh satisfies at a fraction of the complexity.
The order to work in
- Measure first. Performance Analyzer, then DAX Studio. Two minutes.
- Fix the model. Star schema, kill auto date/time, cut high-cardinality columns you don't need, remove bi-directional relationships. This fixes the most and gets skipped the most, because it means rework.
- Fix the worst measure. One at a time, re-measuring after each.
- Fix the page. Fewer visuals, fewer slicers, fewer interactions.
The order matters. Optimising DAX against a badly shaped model is tuning the engine of a car with square wheels.
The uncomfortable part
In our experience the most common root cause isn't on this list. It's that nobody owned the model.
A report gets built by whoever was available, against whatever extract was to hand, under time pressure. It works. Someone adds a page. Someone adds a source. Eighteen months later there are four date tables, three definitions of revenue, a calculated column nobody can explain, and a report that takes eleven seconds to load — and no single person who understands the whole thing well enough to fix it safely.
That's not a performance problem. It's an ownership problem that shows up as a performance problem, and no amount of DAX will resolve it.