DAX patterns that scale: calculation groups, filter context and the measures we stop people writing

Most DAX problems are not DAX problems. They are modelling problems that DAX has been asked to work around, and the resulting measure is complicated because it is compensating for a relationship that should not exist or a grain that was never defined.

That said, there is real DAX craft, and it concentrates in four places: filter context, iteration, time intelligence and measure organisation. This covers each with the patterns we use and the ones we remove.

Filter context is the whole language

Every DAX expression evaluates in a context: the filters applied by the visual, by slicers, by security, and by the measure itself. Nearly every surprising result comes from a misunderstanding of what context a given expression sees.

Two functions matter most.

CALCULATE replaces or modifies filter context. CALCULATE([Sales], 'Product'[Category] = "Tools") does not add a filter to an existing category filter. It replaces the filter on that column. This is the single most common source of "why does my measure ignore the slicer".

To add rather than replace, use KEEPFILTERS:

Tools Sales =
CALCULATE (
    [Sales],
    KEEPFILTERS ( 'Product'[Category] = "Tools" )
)

Now a visual already filtered to a different category correctly returns blank rather than overriding the user's selection.

ALL, ALLSELECTED and ALLEXCEPT remove filters, and the difference between the first two causes more confusion than anything else in the language. ALL removes everything, including the user's slicer selections. ALLSELECTED removes filters from the visual but respects slicers, which is what you almost always want for a percentage-of-total measure:

% of Selected Total =
DIVIDE (
    [Sales],
    CALCULATE ( [Sales], ALLSELECTED ( 'Product' ) )
)

Use ALL for a share of the absolute total regardless of user selection, and label the measure so a reader knows which one they have.

Stop writing twenty near-identical measures

The pattern we remove most often is measure sprawl: Sales, Sales LY, Sales YTD, Sales YTD LY, Sales MAT, Sales vs LY %, and then the same six again for Margin, Volume, Units and Orders. Four base measures and six variants is twenty-four measures, each maintained separately, each a place for the logic to drift.

Calculation groups solve this. You define the base measures once, then define each time variant once as a calculation item, and the two combine at query time.

-- Calculation group: Time Intelligence
-- Item: Current
SELECTEDMEASURE ()

-- Item: Prior year
CALCULATE (
    SELECTEDMEASURE (),
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

-- Item: Year to date
CALCULATE (
    SELECTEDMEASURE (),
    DATESYTD ( 'Date'[Date] )
)

-- Item: Year-on-year %
VAR Current = SELECTEDMEASURE ()
VAR Prior =
    CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    DIVIDE ( Current - Prior, Prior )

Four base measures plus six calculation items replaces twenty-four measures with ten objects, and a correction to the year-on-year logic now happens in one place. Calculation groups require a capacity-tier licence or Tabular Editor to author, and they are worth the dependency on any model that will outlive its first quarter.

The one genuine cost: calculation groups are harder for business users to discover, because the variant is a separate field rather than a named measure. Mitigate it with a clear name, a description, and report templates that pre-apply the common cases.

Time intelligence without surprises

The built-in time intelligence functions work on one condition: a proper date table, marked as the date table, with contiguous dates covering the full range of every fact. Where they misbehave, that condition is almost always unmet.

Two specific traps.

Fiscal calendars. DATESYTD accepts a year-end date, DATESYTD('Date'[Date], "31/03") for a UK fiscal year ending in March. Forgetting it produces a calendar-year figure labelled as fiscal, which is the kind of error that reaches a board pack.

Incomplete periods. A year-on-year comparison in the current month compares a partial month against a complete one, and the variance looks alarming. Make the comparison explicit:

Sales LY (Comparable) =
VAR LastDate = MAX ( 'Fact'[Date] )
RETURN
    CALCULATE (
        [Sales],
        SAMEPERIODLASTYEAR ( 'Date'[Date] ),
        'Date'[Date] <= EDATE ( LastDate, -12 )
    )

Either do that, or label the uncomplicated version clearly. What you must not do is leave a reader to discover the mismatch themselves, because they will discover it in a meeting.

Iteration: where performance goes to die

Iterator functions (SUMX, AVERAGEX, and FILTER with a row-level condition) evaluate once per row in the table they iterate. Over a dimension of 5,000 products that is free. Over a fact table of 200 million rows with a division inside, it is a decision to be slow.

The pattern to look for:

-- Slow: iterates the fact table, computing per row
Margin % (bad) =
AVERAGEX (
    'Sales',
    DIVIDE ( 'Sales'[Revenue] - 'Sales'[Cost], 'Sales'[Revenue] )
)

Two things are wrong. It iterates a large table, and an average of row-level percentages is not the aggregate percentage anyway, a mathematical error, not just a slow one. The correct version aggregates first:

Margin % =
DIVIDE ( [Revenue] - [Cost], [Revenue] )

Where iteration is genuinely required, iterate the smallest table that answers the question. SUMX ( VALUES ( 'Product'[Product Key] ), ... ) over 5,000 products is a different proposition from SUMX ( 'Sales', ... ) over 200 million rows.

Similarly, FILTER on a large table inside CALCULATE forces a row-by-row scan. Where a simple column predicate will do, use it, the engine can push that into the storage engine:

-- Prefer this
CALCULATE ( [Sales], 'Product'[Category] = "Tools" )

-- Over this, when the condition is this simple
CALCULATE ( [Sales], FILTER ( 'Product', 'Product'[Category] = "Tools" ) )

Variables are not optional

VAR is not a style preference. An expression assigned to a variable is evaluated once; the same expression repeated inline is evaluated each time. Variables also make intent legible, which matters more than terseness in a model someone else will maintain.

YoY Growth % =
VAR Current = [Sales]
VAR Prior = CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR Growth = DIVIDE ( Current - Prior, Prior )
RETURN
    IF ( NOT ISBLANK ( Prior ), Growth )

Note the final guard. Returning blank rather than a meaningless growth figure when there is no prior period is a deliberate choice, and it is the choice a finance team will thank you for.

DIVIDE, not the division operator

DIVIDE ( a, b ) returns blank on a zero denominator rather than an error. a / b returns infinity or an error depending on context. Use DIVIDE everywhere, with an explicit third argument where a zero is genuinely the right answer rather than a blank.

How we actually tune a slow model

Guesswork is the enemy. The sequence:

1. Performance Analyzer in Power BI Desktop to find which visual is slow, and whether the time is in the DAX query, the visual render, or "other".
2. Copy the query into DAX Studio, run with server timings, and look at the split between formula engine and storage engine time. Heavy formula-engine time usually means iteration; heavy storage-engine scans usually mean a modelling problem.
3. Check the query plan for the most expensive operations. CallbackDataID in the plan is a signal that the formula engine is calling back into storage row by row, almost always worth removing.
4. Fix the model first, the measure second. Reducing cardinality on a relationship column, removing a bidirectional relationship, or adding an aggregation table often beats any amount of DAX rewriting.
5. Measure again and publish the before and after. A performance claim without numbers is an opinion.

The measures we remove on sight

• Anything with nested IF more than three levels deep. Use SWITCH ( TRUE (), ... ) and it becomes readable.
• IFERROR wrapping a whole measure. It hides the error rather than handling it; find out what is failing.
• Measures that reference a specific hard-coded year or entity name. These silently become wrong, usually in January.
• Measures with no description. If the next developer cannot tell what it means, it will be duplicated rather than reused, which is how estates end up with four definitions of revenue.

Security in DAX, and why it belongs in the model

Row-level security rules are DAX expressions, and they run on every query. A rule that iterates or calls a volatile function will slow the entire model for the roles it applies to, which is a performance problem that only some users experience, the hardest kind to diagnose from a support ticket.

The pattern that performs:

-- On the security dimension, not on the fact
[UserPrincipalName] = USERPRINCIPALNAME ()

The filter then propagates through the relationship to the facts. What to avoid: a rule placed directly on a large fact table, a rule containing FILTER over a fact, or a rule calling LOOKUPVALUE per row. Each of those turns a cheap filter into a scan.

A related trap: USERNAME() and USERPRINCIPALNAME() behave differently in Desktop and in the Service. Test in the Service, with the role applied, from a non-privileged account.

Naming and discoverability are part of the DAX

A measure that cannot be found is a measure that gets rewritten, and that is how an estate acquires four definitions of revenue.

• Prefix nothing with the table name. Sales[Revenue] in a measure name is noise; the measure lives in a display folder.
• Use display folders aggressively. Ten folders of eight measures is navigable; eighty measures in one list is not.
• Write a description on every measure, stating the business definition and any deliberate exclusion. This is what a language model reads, and it is what a new analyst reads.
• Hide anything not meant to be used directly, base measures that only exist as components, technical columns, surrogate keys.
• Set format strings explicitly. A percentage displayed as 0.0834 is a measure nobody trusts, and format inheritance is unreliable across visuals.

A worked tuning example

A client reported a report page taking 24 seconds. The sequence we actually ran:

1. Performance Analyzer showed one visual taking 22 of those seconds, all of it in the DAX query.
2. DAX Studio with server timings showed 90 per cent of the time in the formula engine, with several CallbackDataID entries, a signal that the engine was calling back into storage row by row.
3. The measure was computing a margin percentage with AVERAGEX over the fact table, with a DIVIDE inside the iterator. Two faults: an iteration over 80 million rows, and an average of row-level percentages, which is not the aggregate percentage.
4. The fix was two lines: aggregate revenue and cost separately, then divide once. The arithmetic also became correct, which mattered more than the speed.
5. Result: 22 seconds to 0.4 seconds, with a before-and-after trace attached to the change record.

The lesson we take from this is not about AVERAGEX. It is that the slow measure was also the wrong measure, which is the usual pattern, performance problems and correctness problems tend to have the same root, because both come from someone expressing a calculation at the wrong grain.

Model quality, measure organisation and documentation are the same project, and they are what makes everything built on top, including AI features, work or not work. If you want yours reviewed, a technical review includes a traced performance assessment with before-and-after figures rather than an opinion.