Five DAX Patterns
That Kill Report Performance

A measure that takes 40 milliseconds on a development extract can take eight seconds against production, because the cost of most DAX mistakes scales with cardinality rather than row count. The five patterns below account for most of what I find in slow models.

Each one has a rewrite. None of them require restructuring the model.

Why These Are Expensive

The engine has two halves. The storage engine scans compressed columns and is extremely fast. The formula engine evaluates logic the storage engine cannot express, and is single-threaded and comparatively slow.

Every expensive DAX pattern is a variation of the same mistake: pushing work into the formula engine that the storage engine could have done. When you see a Server Timings split that is 90% formula engine, that is what happened.

1. FILTER Over a Whole Table

The most common, and the easiest to fix. FILTER(Sales, ...) materialises the entire table before applying the condition.

Expensive

High Value =
CALCULATE(
    SUM( Sales[Amount] ),
    FILTER( Sales, Sales[Amount] > 1000 )
)

// Materialises 40M rows, then filters.

Cheap

High Value =
CALCULATE(
    SUM( Sales[Amount] ),
    Sales[Amount] > 1000
)

// A column predicate. Pushed to the storage engine.

Where you genuinely need row context, filter the smallest table that carries it — usually a dimension, not the fact table. FILTER(VALUES(Product[Category]), ...) over a few hundred categories is nothing; the same logic over the fact table is everything.

2. Nested Iterators

An iterator inside an iterator multiplies the work. SUMX over a fact table containing a CALCULATE is the classic version, and it is usually written without anyone realising the cost.

Expensive

Weighted Score =
SUMX(
    Sales,
    Sales[Amount] *
    CALCULATE(
        AVERAGE( Rates[Factor] ),
        Rates[Region] = Sales[Region]
    )
)

// A context transition per row, 40M times.

Cheap

// Resolve the lookup with a relationship instead,
// then the multiplication is a single pass.

Weighted Score =
SUMX(
    Sales,
    Sales[Amount] * RELATED( Rates[Factor] )
)

// Better still: precompute the product upstream
// if the factor does not change per query.

3. Repeated Sub-Expressions

Writing the same expression three times makes the engine evaluate it three times. Variables evaluate once and reuse the result.

Expensive

Growth =
DIVIDE(
    SUM( Sales[Amount] ) - CALCULATE( SUM( Sales[Amount] ), PREVIOUSYEAR('Date'[Date]) ),
    CALCULATE( SUM( Sales[Amount] ), PREVIOUSYEAR('Date'[Date]) )
)

// The prior-year expression runs twice.

Cheap

Growth =
VAR Current = SUM( Sales[Amount] )
VAR Prior   = CALCULATE( SUM( Sales[Amount] ), PREVIOUSYEAR('Date'[Date]) )
RETURN
    DIVIDE( Current - Prior, Prior )

// Each evaluated once. Also considerably easier to read.

4. DISTINCTCOUNT on a High-Cardinality Column

DISTINCTCOUNT is expensive in proportion to the number of distinct values, and it cannot be optimised away. On a column with tens of millions of distinct values it is genuinely slow, and no rewrite changes that.

What you can change is how often it runs. If the same distinct count appears on six visuals across a page, each one computes it independently. Aggregating upstream — a summary table refreshed with the model — turns six expensive queries into six cheap lookups.

It also produces the total that looks wrong but is not: distinct counts do not sum across periods, because a customer active in March and June is one customer for the year and two across the months. Label it on the visual and you will stop answering that question monthly.

5. Long CALCULATE Chains

Each CALCULATE creates a filter context. Nesting several means each layer is evaluated in the context of the one above, and the cost compounds rather than adding.

Most long chains can be flattened into a single CALCULATE with multiple filter arguments, which the engine evaluates together rather than sequentially. Where the logic genuinely needs sequencing, variables let you compute intermediate results once and combine them, rather than nesting.

A Sixth, Worth Knowing: Context Transition in Iterators

Any measure referenced inside an iterator triggers context transition — the engine converts the current row into an equivalent filter context before evaluating. That is one of the most expensive single operations in DAX, and it happens invisibly.

The hidden cost

Total Weighted =
SUMX(
    Sales,
    [Some Measure] * Sales[Quantity]
)

// [Some Measure] triggers a context transition
// for every one of 40M rows. The measure reference
// is what costs, not the multiplication.

Inline it, or aggregate first

Total Weighted =
SUMX(
    Sales,
    Sales[UnitPrice] * Sales[Quantity]
)

// Column references carry no transition cost.
// Where the measure logic is genuinely needed,
// iterate over a summarised table instead of
// the raw fact table.

The rule of thumb: inside an iterator, prefer column references to measure references. Where you must reference a measure, iterate over the smallest table that gives the right grain — a few thousand rows of a summary rather than forty million rows of detail.

When It Is Not the DAX At All

Before rewriting anything, confirm the DAX is actually the problem. The Server Timings split in DAX Studio tells you this in one reading.

If the formula engine dominates, the patterns above apply and rewriting will help. If the storage engine dominates, your measure is fine and the model is the problem — too much data being scanned, poor compression, or high cardinality on a column the measure touches. Rewriting DAX against a storage-engine-bound query is effort spent for almost nothing.

A third case catches people: many storage engine queries with low total time. That means the engine is making repeated small round trips rather than one scan, which usually points at a measure it cannot fold into a single query. Restructuring often collapses a dozen queries into one, and the total time drops far more than the individual query times suggest it should.

Measuring the Difference Properly

1. Clear the cache in DAX Studio before every run. A warm cache makes any change look like an improvement.
2. Take three readings and use the median, not the fastest.
3. Change one thing at a time. Rewriting three measures at once teaches you nothing transferable.
4. Record the Server Timings split, not just the total. A change that moves work from formula engine to storage engine is the one that will keep scaling.
5. Test against production-scale cardinality, not a development extract.

Fix Them in This Order

If you have all six patterns in one model, and many models do, the order you address them in matters because the effort differs enormously.

Start with FILTER over tables. It is a one-line change, it is unambiguous, and on a large fact table it frequently produces the biggest single improvement of anything on this list. You can usually do every instance in an afternoon.

Then repeated sub-expressions. Converting to variables is mechanical, carries almost no risk of changing behaviour, and makes the remaining work easier to read.

Then context transition and nested iterators. These require more thought because the fix sometimes changes the grain the measure operates at, so each one needs validating against its current output before and after.

Leave DISTINCTCOUNT and CALCULATE chains last. Both often need a model change rather than a measure rewrite — a summary table, or a restructured relationship — and that is a larger piece of work you want to scope rather than improvise.

Working in that order means the cheap wins land first, which matters if anyone is watching the report speed while you work.

What We Would Do

A performance audit identifies which measures are actually costing you — frequently not the ones people suspect — and rewrites them with the before-and-after timing recorded for each. The documentation is the part clients keep.