The idea
Every time a reader clicks a slicer, each visual on the page asks the model a question in DAX. Two engines answer. The storage engine is fast: it scans compressed columns, in parallel, and returns small results. The formula engine is clever: it understands the whole of DAX, runs iterators and the odd logic, and works one row at a time. A fast report is one where the storage engine does almost all the work and the formula engine only stitches the answers together.
That is the single idea behind nearly every performance fix. The rest is learning which choices keep the work in the fast engine and which push it into the slow one.
Figure 1
What happens when a visual refreshes
- Visual Sends a DAX query for each measure
- Formula engine Understands DAX, works row by row, single-threaded
- Storage engine Scans compressed columns, parallel, very fast
- Result Numbers come back to the visual
- A slicer click makes every visual on the page send a DAX query. Performance analyzer (**View → Performance analyzer**) times each one.
- The formula engine takes the query and plans it. It understands all of DAX, and it is also the slower of the two: it works one row at a time on a single thread.
- Whatever can be phrased as "scan these columns, filter on these values, add this column up" is handed to the storage engine, which does it in parallel on compressed data.
- The storage engine returns small results, and the formula engine finishes. The fewer round trips and the less row-by-row work, the faster the visual.
Why it exists
The split is the reason Power BI can scan millions of rows in a blink. The storage engine (VertiPaq) trades flexibility for speed: it can only do simple things (filter on a column, add a column up, group), and it does them on data stored column by column and compressed. Anything more elaborate has to go back to the formula engine.
For the author, that means two kinds of slow. A measure is slow when it forces the formula engine to walk rows. A model is slow when it is bloated: too many columns, columns with too many distinct values, long text where a number would do, history nobody looks at. Harbor & Pine's "slow dashboard" is almost never the visuals themselves.
How it works under the hood
Measures: let the storage engine do the filtering
The classic slow measure walks a whole table with FILTER and evaluates a test on each row. The
fast version states the same filter as column conditions inside CALCULATE, which the storage
engine understands directly.
Figure 2
The same Web Sales, two ways to get it
| order_item_id | channel | status | line_total |
|---|---|---|---|
| 19660 | web | completed | 54.99 |
| 19663 | web | completed | 189.00 |
| 19749 | web | cancelled | 189.00 |
| 19675 | app | completed | 219.96 |
| Web Sales |
|---|
| $243.99 |
SUMX(FILTER(order_items, RELATED(orders[channel]) = "web" && RELATED(orders[status]) = "completed"), order_items[line_total]). The formula engine visits each line in turn.- It evaluates two
RELATEDlook-ups and a test on every line: kept, kept, dropped (cancelled), dropped (app). On a table of 30,338 lines that is the work of the slow engine, line after line. CALCULATE(SUM(order_items[line_total]), orders[channel] = "web", orders[status] = "completed")sends two simple column filters and a sum to the storage engine, which answers in one scan.- Same $243.99. The numbers match; only the path differs. That is why a slow measure can be perfectly correct.
Remember that a measure is not run once. A matrix with six category rows, a total and a slicer evaluates it seven times, and a page with eight visuals runs those queries together on every click. A slow pattern that looks harmless in one cell is multiplied by every cell and every visual, which is why the same measure can feel fine in a card and unusable in a big matrix. Test slow measures in the busiest visual, not the simplest one.
The rule of thumb: filter with CALCULATE(..., table[column] = value) and keep FILTER for
conditions that really need row logic, such as comparing two columns, and then filter the smallest
table you can. Iterators are fine on small tables and on simple expressions.
Model: fewer columns, lower cardinality
Compression depends on cardinality, the number of distinct values in a column. The storage engine keeps a dictionary of values and a code per row, so a column with three values costs almost nothing and a column of unique ids costs the most.
Figure 3
Distinct values per column (more = harder to compress)
- The storage engine stores each column as a dictionary of its distinct values plus a short code per row. Columns with a few values, like
channelorstatus, shrink to almost nothing. customer_idhas 1,771 distinct values on 12,000 orders, andorder_idhas one per order. A column of unique values has nothing to compress.- Every column you load costs memory and scan time, so drop what no visual uses, and keep ids in integer columns, not long text.
Practical consequences: remove columns no visual or relationship needs, use whole numbers instead of long text for keys, round decimals the report does not show, and split a datetime into a date and a time if you only slice by date. Never load a column "just in case".
Imports: bring in only what the report uses
A lean import filters rows and drops columns in Power Query before they reach the model. If the steps can be translated to SQL, Power Query folds them into the source query, so the database does the work and only the reduced result travels. Check by right-clicking an applied step: if View Native Query is available, it folds. A step that breaks folding (a custom function, an index column) makes everything after it run on your machine, so do the filters and column removal before it.
Storage mode and relationships
Import copies data into VertiPaq and is the fastest. DirectQuery leaves the data at the source and sends a query for every click: it suits data too big or too fresh to import, and it is slower. A composite model mixes the two. Between tables, prefer single-direction, many-to-one relationships: they are quicker and unambiguous, and a bi-directional one can make filters travel paths you did not intend.
VAR: work it out once
If a measure uses the same expression three times, DAX may compute it three times. VAR names the
result and uses it wherever you refer to it, so it is computed once and the formula also reads
better.
Where to measure it in Power BI Desktop
Open View → Performance analyzer, start recording, click through the page, and read the timings: DAX query, visual display and other. Copy query pastes the DAX into DAX query view or DAX Studio. Change one thing at a time and measure again, because the only reliable way to know a fix worked is a number.
Common misconceptions
"If the numbers are right, the measure is fine"
Correctness and speed are separate properties. The SUMX(FILTER(...)) version and the CALCULATE
version return the same $243.99 and take very different amounts of work. A report can be right in
every cell and still be one nobody opens.
"A slow report needs a bigger machine or a Premium capacity"
More hardware makes a bad pattern less bad, but the cost scales with the data. A model with unneeded columns and row-by-row measures will be slow on a bigger machine too, just later. Fix the pattern first; it is cheaper and lasts.
"DirectQuery is faster because the data is not copied"
Not copying data saves refresh time and memory, not query time. Every click becomes a query to another system over a network. Import with a sensible size is almost always faster for the reader.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. A copy of the channel on every line bloats the model with a high-volume column and still walks the table row by row. The cost is in the pattern, not in RELATED.Try another answer.
Right. A simple column filter is something the storage engine can do in one scan, with no row-by-row work. Same numbers, far less for the formula engine to do.
Not quite. DirectQuery usually makes a measure slower, because each click becomes a query to another system. It does not change the row-by-row pattern.Try another answer.
Not quite. VAR computes an expression once, which helps when it is used several times. Here it is used once, and the slow part would run exactly the same.Try another answer.
Lesson done: it is ticked on the map.