DAX and performance

DAX in one idea: filter context

Every DAX measure is evaluated inside a filter context — the set of filters applied by the visual's rows, columns, slicers and the report page. CALCULATE is the function that modifies that context. Almost every DAX bug or slowdown is a filter-context misunderstanding: a measure returns the wrong total, or recalculates far more than it needs to, because the context isn't what the author assumed.

Two engines under the hood

A DAX query is split between:

• Storage Engine (SE) — fast, multi-threaded, cached; does the bulk scanning/aggregation against VertiPaq. Work you want here.
• Formula Engine (FE) — single-threaded; handles logic the SE can't. Work that spills to the FE, or forces row-by-row evaluation, is where queries get slow.

The performance goal is to keep work in the fast storage engine and minimise single-threaded formula-engine and materialisation.

Classic performance killers

• Iterators over big tables (SUMX/FILTER row-by-row) where a simple aggregation would do.
• High-cardinality columns dragged into visuals — millions of distinct values to render/aggregate.
• Too many visuals per page — each is a separate query; a 20-visual page fires 20 queries per interaction.
• Bidirectional relationships and complex FILTER forcing large intermediate results.
• Calculated columns doing measure work, bloating the model (see the semantic-model concept).
• Whole-model-in-one-visual matrices with no aggregation.

The diagnostic workflow

1. Performance Analyzer (in Desktop) — records each visual's DAX query time, visual render time, and 'other'. It tells you WHICH visual is slow and whether the cost is the query or the rendering.
2. DAX Studio / query traces — take the captured query and see the SE vs FE split, scan counts, and where time goes.
3. Fix the model or the measure, not the visual chrome.

Failure modes

• Blaming the visual/capacity when the real cost is one runaway measure.
• Slow-report-fixed-by-bigger-capacity — throwing CU at a modelling problem; it masks the symptom and inflates cost.
• DirectQuery + heavy DAX — every interaction is a live, translated query against the source; the worst of both worlds.

What to watch (proofs)

• Query-level performance: capacity-metrics and query diagnostics surface the slowest, most CU-hungry queries per semantic model — the objective 'what is actually slow'.
• Model shape as a proxy: the admin scanner (admin/workspaces/getInfo) exposes measures, calculated columns and bidirectional relationships — the modelling smells that cause slow DAX.
• Report complexity: reports/pages with excessive visual counts, from the scan — the per-page query multiplier.
• Capacity correlation: Get-PowerBIActivityEvent plus the metrics app tie slow user experiences to specific reports/models and to capacity throttling windows.