6.3 DAX Optimization Patterns

Quick Answer: Use VAR to avoid redundant calculations. Prefer DIVIDE() over IF/division patterns. Use direct column filters in CALCULATE instead of FILTER(). Avoid DISTINCTCOUNT on million-value columns. Replace IF(condition, SUM(), 0) with CALCULATE(SUM(), condition). These changes can cut measure time substantially.

Storage Engine vs. Formula Engine

Understanding why a pattern is fast helps you optimize deliberately. Power BI splits work between two engines: the storage engine (VertiPaq/SE), which is multi-threaded, cached, and very fast at scanning and aggregating columns, and the formula engine (FE), which is single-threaded and handles logic the SE cannot. Optimizing DAX largely means keeping work in the storage engine and minimizing row-by-row formula-engine iteration. Direct column filters, SUM, and simple aggregations stay in the SE; FILTER over a table and complex iterators push work to the FE.

Variables Eliminate Redundancy

// Before, SUM(Sales[Revenue]) evaluated twice
Profit Margin = DIVIDE(SUM(Sales[Revenue]) - SUM(Sales[Cost]), SUM(Sales[Revenue]))

// After, each aggregation evaluated once
Profit Margin =
VAR Revenue = SUM(Sales[Revenue])
VAR Cost = SUM(Sales[Cost])
RETURN DIVIDE(Revenue - Cost, Revenue)

Efficient Filtering in CALCULATE

// Slow, FILTER iterates row by row (formula engine)
Premium Sales = CALCULATE(SUM(Sales[Amount]), FILTER(Products, Products[Price] > 100))

// Fast, direct column filter (storage engine)
Premium Sales = CALCULATE(SUM(Sales[Amount]), Products[Category] = "Premium")

Rule: Use FILTER() only when the condition must reference a measure or combine multiple columns. For simple column comparisons, use direct filters.

Avoid Unnecessary Iterators

Total Revenue = SUMX(Sales, Sales[Amount])    // slow, needless iteration
Total Revenue = SUM(Sales[Amount])            // fast, pure storage engine

Reserve SUMX for genuine per-row math:

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

Replace IF Patterns with CALCULATE

// Inefficient
Active Sales =
IF(HASONEVALUE(Products[Status]),
   IF(VALUES(Products[Status]) = "Active", SUM(Sales[Amount]), 0),
   SUM(Sales[Amount]))

// Efficient
Active Sales = CALCULATE(SUM(Sales[Amount]), Products[Status] = "Active")

SELECTEDVALUE over Complex Patterns

// Verbose
Selected Region = CALCULATE(MAX(Regions[RegionName]), FILTER(Regions, COUNTROWS(Regions) = 1))
// Simple and efficient
Selected Region = SELECTEDVALUE(Regions[RegionName], "Multiple")

Distinct Counts on High-Cardinality Columns

DISTINCTCOUNT on a column with millions of distinct values is expensive. In DirectQuery models you can use APPROXIMATEDISTINCTCOUNT, which uses a HyperLogLog-style algorithm for a much faster approximate result. Note the important limitation: APPROXIMATEDISTINCTCOUNT works only in DirectQuery mode (against supported sources such as Azure SQL, Synapse, Snowflake, Databricks, and BigQuery), not in Import models. In Import mode, reduce the cost instead by pre-aggregating, lowering cardinality, or limiting the column's role.

Quick Optimization Checklist

On the Exam

The PL-300 tests recognizing inefficient DAX and suggesting improvements, knowing when FILTER() is necessary vs. direct filters, using variables for readability and speed, choosing SUM vs. SUMX, and the storage-engine vs. formula-engine implications.

Optimize by Steering Work to the Storage Engine

The unifying principle behind every pattern here is the two-engine architecture. The storage engine is fast, cached, and parallel; the formula engine is single-threaded and handles whatever the storage engine cannot. Fast DAX maximizes storage-engine work (column scans, simple aggregations, boolean filters) and minimizes formula-engine iteration (row-by-row FILTER, complex iterators, repeated re-evaluation). When you replace a table FILTER with a column predicate, swap SUMX for SUM, or hoist a repeated expression into a VAR, you are moving work from the slow engine to the fast one or eliminating it entirely.

Naming the engines explicitly helps you predict which rewrite will actually help.

Worked Rewrite: Conditional Aggregation

Consider a measure meant to total only active products. A beginner writes nested IFs around HASONEVALUE and VALUES, forcing the formula engine to branch per context. The optimized form, CALCULATE(SUM(Sales[Amount]), Products[Status] = "Active"), pushes the whole job to the storage engine: the boolean predicate becomes a fast single-column filter and SUM aggregates in parallel. Same result, dramatically less work. This rewrite, IF-around-SUM into CALCULATE-with-predicate, is one of the most testable optimization patterns because it appears constantly in real models.

When the Bottleneck Is the Model, Not the Measure

Not every slow measure is a formula problem. If a measure must DISTINCTCOUNT a million-value column, or scan a fact table with poor compression, no amount of VAR tidying will save it, the fix is upstream: lower the column's cardinality, pre-aggregate, or in DirectQuery use APPROXIMATEDISTINCTCOUNT (DirectQuery only). The professional habit is to confirm with Performance Analyzer and DAX query view whether time is going to the storage engine (a model/cardinality issue) or the formula engine (a DAX-logic issue) before deciding which lever to pull.

A Diagnostic-First Mindset

Strong optimizers resist guessing. Before rewriting anything, capture the visual's query with Performance Analyzer and run it in DAX query view (or DAX Studio) to see whether the cost is storage-engine time (large scans, high cardinality, distinct counts) or formula-engine time (row-by-row iteration, repeated re-evaluation). Storage-engine-bound queries are usually model problems, fixed by lowering cardinality, pre-aggregating, or removing columns; formula-engine-bound queries are usually DAX problems, fixed by VARs, column predicates instead of table FILTERs, and SUM instead of needless SUMX.

Applying one targeted change and re-measuring, rather than bundling several speculative edits, is both better engineering and the disciplined approach the exam's optimization scenarios assume. The recurring lesson is that the same symptom (a slow visual) can have opposite cures depending on which engine is doing the work.