Level 1 · The storage engine and the formula engine

When a report is slow, “Power BI is slow” is the least useful diagnosis. Behind every visual there is a chain: a query is sent, DAX is evaluated, data is scanned, the visual is drawn. One weak link slows everything, and the first job is to find it.

What you are working in

This is a quiz. The levels after it give you measures to rewrite in a practice engine on this page. To time a real report in Power BI Desktop: Optimize → Performance analyzer (on older versions View → Performance analyzer), Start recording, then Refresh visuals or click a slicer. Each visual gets a line with its total time, split into DAX query, Visual display and Other (waiting for other visuals). A big DAX query number points at the model or the measure; a big Visual display number points at a visual with too many points.

Two engines behind every DAX query

The storage engine (SE). For an Import model, this is VertiPaq, the in-memory columnar store. Each column is compressed separately, and the storage engine scans those columns very fast and on every CPU core. It is good at a few simple things: filter a column by values, group by columns, and aggregate with SUM, COUNT, MIN, MAX and DISTINCTCOUNT. In DirectQuery, the same role is played by the source database receiving SQL.

The formula engine (FE). It reads your DAX, works out a plan, sends the simple parts to the storage engine and does the rest itself: complex expressions per row inside iterators, IF and SWITCH logic, context transition, combining the results. It runs on a single thread, and it works on uncompressed intermediate tables.

What pushes work to the slow engine

• FILTER over a whole fact table with conditions the storage engine can’t take as a simple column filter, then an iterator over the result.
• Iterating millions of rows and calling a measure for each row (a context transition per row).
• Large intermediate tables the formula engine has to build and hold.

A measure like CALCULATE(SUM(order_items[line_total]), orders[status] = "completed") is a column filter plus a sum: almost all storage engine. The same number written as SUMX(FILTER(order_items, RELATED(orders[status]) = "completed"), order_items[line_total]) builds a filtered table of order lines first. On 30,000 rows nobody notices; on 30 million the gap is often ten to a hundred times.

Seeing the split

In Performance analyzer, Copy query on the slow visual and paste it into DAX query view (the fourth icon on the left rail) or into the free tool DAX Studio, where Server Timings shows how many milliseconds went to each engine and how many storage engine queries ran.

Common mistakes

• Optimising before measuring. Find the slow visual first.
• Testing on a small sample. Problems show up at full data volume.
• Too many visuals on a page. Each one sends its own query; twenty cards are twenty queries.