Power BI

DAX That Runs Fast, and the Habits That Get You There

By Syed Hussnain Sherazi | August 19, 2025 | DAX | Performance | Power BI

Practical DAX optimisation habits for faster measures, cleaner models, and better diagnostics.

Most slow DAX is not slow because the language is hard. It is slow because the author has not yet developed an intuition for how the formula engine and the storage engine cooperate. Once you understand what the engine is actually doing, the rules of fast DAX become obvious, and the same five or six patterns will solve the majority of performance problems you encounter.

This article walks through those patterns with worked examples, and with the diagnostic process you should use whenever a measure stops scaling. Everything here can be applied today inside DAX Studio against any Power BI dataset.

The Two Engines Story

flowchart LR
  subgraph Report["Report interaction"]
    VISUAL["Visual query"]
    CONTEXT["Filter context"]
  end
  subgraph Formula["Formula engine"]
    PLAN["Query plan"]
    ITERATE["Row-by-row logic"]
    COMBINE["Combine results"]
  end
  subgraph Storage["Storage engine / VertiPaq"]
    SCAN["Column scans"]
    FILTER["Compressed filters"]
    AGG["Aggregations"]
  end
  subgraph Result["User result"]
    CACHE["Reusable cache"]
    RESPONSE["Fast visual response"]
  end
  VISUAL --> CONTEXT --> PLAN
  PLAN -->|"simple filters and aggregations"| SCAN
  SCAN --> FILTER --> AGG --> COMBINE
  PLAN -->|"complex iterators"| ITERATE --> COMBINE
  COMBINE --> CACHE --> RESPONSE
  DIAG["DAX Studio timings and query plan review"] -.-> Formula
  DIAG -.-> Storage

Every DAX query is processed by two engines. The storage engine, often called VertiPaq, is fast, columnar, and parallel. It scans data and returns aggregated results. The formula engine is the orchestrator. It plans the query, calls the storage engine for chunks of data, then performs row by row computations on the results.

Fast DAX maximises the work done in the storage engine and minimises the work done in the formula engine. Slow DAX does the opposite. It pulls millions of rows into the formula engine and processes them in memory.

You can see this split for any query in DAX Studio. Switch on Server Timings, run the query, and look at the duration columns. If most of the time sits in SE (storage engine), the query is healthy. If FE (formula engine) dominates and the SE shows millions of rows returned, the formula engine is doing work it should not be doing.

Pattern 1, Avoid Calculated Columns on the Fact Table

A calculated column is computed at refresh time and stored uncompressed. On a small dimension this is fine. On a fact table with hundreds of millions of rows, it inflates the model and ruins compression. The same logic written as a measure runs only when needed and uses the storage engine efficiently.

-- Wrong, calculated column on FactSales
Profit Column =
FactSales[Quantity] *
(FactSales[UnitPrice] - FactSales[UnitCost]) *
(1 - FactSales[Discount])

-- Right, measure
Profit =
SUMX(
    FactSales,
    FactSales[Quantity] *
    (FactSales[UnitPrice] - FactSales[UnitCost]) *
    (1 - FactSales[Discount])
)

In a real model with 200 million rows, this single change reclaimed 1.4 GB of memory and shaved several seconds off cold queries.

Pattern 2, Variables Are Cheap, Use Them Liberally

Variables compute once per scope and reuse the value. Without them, the engine often recalculates the same expression several times within a single measure.

-- Without variables, sum is computed three times
Sales Variance Slow =
DIVIDE(
    SUM(FactSales[TotalAmount]) - CALCULATE(SUM(FactSales[TotalAmount]), DATEADD(DimDate[Date], -1, YEAR)),
    CALCULATE(SUM(FactSales[TotalAmount]), DATEADD(DimDate[Date], -1, YEAR))
)

-- With variables, computed once
Sales Variance Fast =
VAR CurrentSales = SUM(FactSales[TotalAmount])
VAR PriorYearSales = CALCULATE(SUM(FactSales[TotalAmount]), DATEADD(DimDate[Date], -1, YEAR))
RETURN DIVIDE(CurrentSales - PriorYearSales, PriorYearSales)

The faster version also reads better. Variables are the single biggest improvement to DAX legibility since the language was introduced.

Pattern 3, Use DIVIDE, Not Slash

The division operator throws an error on divide by zero. DIVIDE handles it gracefully, and the engine can optimise the path because the semantics are explicit. Always prefer DIVIDE.

-- Avoid
Margin = SUM(FactSales[Profit]) / SUM(FactSales[TotalAmount])

-- Prefer
Margin = DIVIDE(SUM(FactSales[Profit]), SUM(FactSales[TotalAmount]))

You can pass a third argument to DIVIDE for the alternate result when the denominator is zero. The default is BLANK, which is usually the right choice for visuals.

Pattern 4, FILTER Should Be the Last Resort

A common mistake is to wrap CALCULATE arguments in FILTER unnecessarily.

-- Slower, FILTER iterates the whole table row by row
North America Sales Slow =
CALCULATE(
    [Total Sales],
    FILTER(DimGeography, DimGeography[Region] = "North America")
)

-- Faster, simple boolean filter is converted to a column filter by the engine
North America Sales Fast =
CALCULATE(
    [Total Sales],
    DimGeography[Region] = "North America"
)

The second form is converted by the engine into a fully optimised storage engine filter. The first form forces the formula engine to materialise the dimension and iterate it.

There is one situation where FILTER is correct. When you need to filter on a measure or on a complex expression, FILTER is required. Reserve it for those cases.

Pattern 5, Replace Iterators With Aggregations Where Possible

Iterators such as SUMX, AVERAGEX and COUNTX are powerful but they walk the table row by row. When the same result can be obtained with a simple aggregation, prefer the aggregation.

-- Slower if FactSales has hundreds of millions of rows
Total Quantity Slow = SUMX(FactSales, FactSales[Quantity])

-- Faster, the storage engine does this in one pass
Total Quantity Fast = SUM(FactSales[Quantity])

Iterators earn their keep when the row level expression involves multiple columns or a non additive operation. The Profit measure earlier is a legitimate use of SUMX because the per row product cannot be expressed as a simple SUM.

Pattern 6, Be Careful With Distinct Counts

DISTINCTCOUNT is one of the most expensive operations in DAX. The engine has to track unique values across the filter context, which gets costly on high cardinality columns. There are three tactics that help.

The first tactic is to use COUNTROWS on a precomputed distinct list when the cardinality is moderate. The second is to use the new generation of DISTINCTCOUNT optimisations on Fabric capacities, which include partitioned cardinality estimation. The third is to consider whether the question really requires an exact count, or whether an approximate count via APPROXIMATEDISTINCTCOUNT is acceptable.

-- Exact, expensive on huge tables
Active Customers Exact = DISTINCTCOUNT(FactSales[CustomerID])

-- Approximate, dramatically faster on huge tables, error rate around 2 percent
Active Customers Approx = APPROXIMATEDISTINCTCOUNT(FactSales[CustomerID])

For dashboards where the user wants a sense of scale, the approximate version is almost always good enough.

Pattern 7, Time Intelligence the Right Way

The shipped time intelligence functions such as SAMEPERIODLASTYEAR are convenient but they assume a contiguous date table marked correctly. When this assumption holds, they are fast. When it does not, they fall back to slower paths.

The portable, predictable approach uses CALCULATE with date ranges.

Sales LY =
CALCULATE(
    [Total Sales],
    DATEADD(DimDate[Date], -1, YEAR)
)

Sales LY Smart =
VAR MaxDate = MAX(DimDate[Date])
VAR MinDate = MIN(DimDate[Date])
RETURN
CALCULATE(
    [Total Sales],
    ALL(DimDate),
    DimDate[Date] >= EDATE(MinDate, -12),
    DimDate[Date] <= EDATE(MaxDate, -12)
)

The second form is more verbose but it gives explicit control over the range, and it is easier to debug when something goes wrong with month end alignment.

Pattern 8, Watch Out for Bidirectional Relationships

A bidirectional relationship lets filters propagate both ways across a join. This is sometimes necessary but it has two side effects. Queries become more expensive because the filter context expands. Ambiguity can emerge when more than one path exists between two tables, leading to results that are hard to predict.

Most of the time, the alternative is to keep relationships unidirectional and use CROSSFILTER inside CALCULATE for the rare cases that need the reverse path.

-- Use bidirectional only for this measure
Customers Who Bought Product =
CALCULATE(
    DISTINCTCOUNT(DimCustomer[CustomerID]),
    CROSSFILTER(FactSales[CustomerID], DimCustomer[CustomerID], BOTH)
)

This pattern keeps the model clean and predictable while still allowing the specific query to traverse the relationship in both directions.

A Diagnostic Checklist

When a measure is slow, work through the same routine every time.

Open DAX Studio and connect to the model. Capture the slow query using the Query Plan and Server Timings tabs. Examine where the time is going. If formula engine time dominates, look for iterators over large tables, complex FILTER expressions, or unnecessary CONTEXT switches. If storage engine time dominates, look for missing relationships, missing indexes (in DirectQuery mode), or oversized tables that should be partitioned.

Run VertiPaq Analyzer against the model. Inspect column cardinality, encoding, and dictionary size. The fact table columns should have small dictionaries. Surrogate keys should be integer encoded. If a text column has a million unique values and it sits on a fact table, that is a candidate for redesign.

Test variants of the measure side by side. DAX Studio supports running multiple queries in sequence with Server Timings clearing the cache between runs. Use this to compare two formulations of the same measure and prove which one is faster.

When Optimising Stops Mattering

There is a point at which optimising DAX further yields diminishing returns. If your query is already running in 200 milliseconds, the next round of tuning rarely justifies the engineering hours. Focus optimisation effort on the queries that users actually wait on, not on the ones that already feel snappy.

You can identify those queries through Performance Analyzer in Power BI Desktop, or through the usage metrics in the service. Spending an afternoon on the slowest five percent of queries is almost always worth more than an entire day on minor improvements to fast queries.

A Closing Habit

The single best habit you can develop is to write the simplest possible version of a measure first, capture its query plan, and only then optimise. Many developers reach for clever patterns immediately, which produces unreadable code that is no faster than the simple version would have been. Simple is fast often enough that you should make it the default and let the diagnostics tell you when something more complex is justified.

References and Further Reading