Lakshya

Analytics & BI Tools · Chapter 10 of 18

Power BI internals: the model, and what DAX is really doing

The engine, the two evaluation contexts, and the three mistakes that account for most slow or wrong reports.

7 min read1 diagramAll 18 chapters

Power BI is easy to start with and unusually deep underneath, and the gap between those two facts is where most organisations get stuck. The people who can actually fix a broken report understand two things: how the storage engine works, and what a measure is.

Two engines, and which one you want your query in

Every query runs through two components, and the distinction is the single most useful diagnostic frame in the whole product:

  • The storage engine operates on the compressed columnar data. It is multi-threaded, extremely fast, and can only do simple things — scan, filter, group, and simple aggregations. Work done here is nearly free.
  • The formula engine handles everything the storage engine cannot: complex logic, iteration, anything row-by-row. It is single-threaded and it works on uncompressed data that the storage engine had to hand it.
  • So the whole performance question is: how much work happened in the storage engine, and how many rows did it have to materialise for the formula engine? A query that runs entirely in the storage engine is fast regardless of data volume. A query that pulls a million rows out to iterate is slow regardless of how good your hardware is.

This is why the standard diagnostic is to capture the generated storage-engine queries and look at the row counts. One query returning a handful of rows is healthy. One query returning millions, followed by a long formula-engine step, is your problem — and you have found it in a minute rather than by guessing.

What a measure actually is

A MEASURE IS NOT A FORMULA. IT IS A FORMULA PLUS THE FILTER THE VISUAL SUPPLIES. Revenue := SUM(Sales[Amount]) one definition, written once row: North, 20264,210row: South, 20261,880total row6,090 the visual filters the model, then the measure evaluates against what survives so the total row is NOT the sum of the rows — it is re-evaluated TWO CONTEXTS, AND THE BRIDGE filter context — which rows are visible row context — which row we are on CALCULATE is the only thing that modifies filter context — and it also converts row context into filter context THE THREE MISTAKES THAT CAUSE MOST SLOW OR WRONG REPORTS calculated column computed at refresh, stored, adds cardinality to the model use a measure unless you must slice on it row-by-row iteration an iterator over a huge fact table materialises far more than you think push the work into the aggregation bidirectional filters set everywhere “to make it work” creates genuinely ambiguous paths one direction, plus explicit CALCULATE The diagnostic that settles it: capture the generated storage-engine query. If one query returns millions of rows to be processed rowby row, you have found the problem — the fast path pushes work into the compressed columnar scan, and the slow path pulls data out of it to iterate.
The same measure returns different numbers in different cells because the visual supplies the filter. Note the consequence on the right: the total row is re-evaluated, not summed — which is why totals sometimes look 'wrong' and are not.

A measure is not a formula that computes a value. It is a formula that computes a value within a filter context supplied by wherever it is displayed. Once that clicks, most of the language stops being mysterious.

  • Filter context is the set of rows currently visible — contributed by the row and column headers of the visual, by slicers, by page filters, and by anything else filtering the report.
  • Row context exists inside a calculated column or an iterator, and means ‘the current row’. It does not filter anything on its own, which is the detail that surprises people.
  • Context transition is the bridge, and it is what the CALCULATE function does beyond its obvious job: it converts the current row context into an equivalent filter context. This is why wrapping an expression in CALCULATE can change its result dramatically, and why an implicit measure reference inside an iterator behaves differently from the same expression written inline.

The practical consequence worth stating plainly: a total row is not the sum of the rows above it. It is the same measure re-evaluated with a different filter context. For a sum those coincide; for a distinct count, an average, or any ratio, they do not — and explaining that to a business user who thinks the report is broken is a recurring part of the job.

The three mistakes that cause most problems

1. A calculated column where a measure belongs. A calculated column is computed once at refresh and stored in the model, so it adds cardinality and size permanently — and a high-cardinality calculated column on a large fact table can single-handedly double a model. A measure is computed at query time and stores nothing.

The rule: use a measure unless you genuinely need to slice, filter or group by the value. If it appears on an axis or in a slicer, it must be a column. If it only ever appears as a number, it should be a measure.

2. Row-by-row iteration over a large fact table. Iterator functions evaluate an expression per row. Over a dimension of a few hundred rows that is free; over a hundred-million-row fact table it forces the formula engine to do the work the storage engine should have done. The fix is almost always to restructure so the aggregation happens inside the scan — or to precompute the value upstream, which is frequently the right answer and the one people resist.

3. Bidirectional relationships set everywhere. They are enabled to make one visual work and then left on, and they create genuinely ambiguous filter paths — the engine cannot determine which route a filter should take. Most ‘the numbers are wrong’ reports in a mature estate are relationship ambiguity, not arithmetic. Prefer single-direction relationships plus an explicit CROSSFILTER in the one measure that needs it, so the exception is visible and local rather than global and silent.

Refresh, and the change that makes large models viable

A full refresh reloads everything. For a large fact table that is both slow and unnecessary, since historical data does not change. Incremental refresh partitions the table by date and reloads only recent partitions.

Two details that decide whether it works. It relies on the source honouring the date predicate efficiently — if the filter is not pushed down to the source, you have gained nothing and added complexity. And it requires the historical data genuinely to be immutable; late-arriving corrections to old periods will be silently missed, which is a data-quality bug that surfaces months later as an unexplained discrepancy.

The related capability worth knowing about is a mode that reads directly from open table formats in the lake without either importing or generating a warehouse query per interaction. It is genuinely useful and it has a specific caution: it can silently fall back to ordinary query mode when a query exceeds a guardrail, and the user simply experiences a slow report with no indication why. If you deploy it, monitor the fallback rate as a first-class metric rather than trusting the mode name.

Where Power BI genuinely hurts

  • Version control and code review. Report files have historically been binary, so diffing and reviewing changes is awkward. Newer project formats improve this considerably and adoption is uneven — if you are setting up a new estate, choose the text-based format from day one, because retrofitting it across hundreds of reports is a project.
  • Governance drift. The same accessibility that drives adoption produces hundreds of near-duplicate models, each with its own definition of revenue. This is not a tool defect; it is the predictable consequence of self-service without a shared semantic layer, and it is covered in the next chapter.
  • Capacity behaviour. Capacity-based licensing smooths and bursts usage, then throttles when a sustained overdraft accumulates — so the symptom is that reports become slow for everyone some time after the expensive thing ran. Diagnosing that without understanding the smoothing model is close to impossible, and it is the most common confusing operational issue on the platform.

Check yourself

What is the single most useful Power BI diagnostic?Capture the generated storage-engine queries and look at the row counts. Work done in the storage engine is nearly free; rows materialised for the single-threaded formula engine are where slowness lives.
Calculated column or measure?A measure unless you need to slice, filter or group by the value. A calculated column is stored, adds cardinality permanently, and can double a model on its own.
Why is the total row not the sum of the rows?It is the same measure re-evaluated in a different filter context. For a sum they coincide; for distinct counts, averages and ratios they do not.
What usually causes 'the numbers are wrong' in a mature model?Relationship ambiguity from bidirectional filters left on everywhere, not arithmetic. Prefer single-direction relationships with an explicit local override.
← How a BI engine actually worksTableau internals: the engine, and the order of operations →