DAX Performance Optimization

Why DAX performance matters

The two engines

Every DAX query is processed by two engines:

Engine	What It Does	Speed
Storage Engine (SE)	Reads data from VertiPaq (or Direct Lake/DirectQuery). Handles simple filters and aggregations.	Very fast — in-memory scans
Formula Engine (FE)	Evaluates complex DAX expressions (iterators, nested CALCULATE, cross-table calculations).	Slower — single-threaded, row-by-row

Optimization goal: Push as much work as possible to the Storage Engine. Minimize Formula Engine usage.

Common DAX performance problems

1. Unnecessary iterators

// SLOW: Iterator processes every row
Bad Measure = SUMX(Sales, IF(Sales[Category] = "Grocery", Sales[Amount], 0))

// FAST: Filter then aggregate (Storage Engine handles it)
Good Measure = CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Grocery")

Why: SUMX with IF forces the Formula Engine to evaluate every row. CALCULATE with a filter pushes the work to the Storage Engine.

2. Nested CALCULATE

// SLOW: Redundant outer CALCULATE — unnecessary context transition
Bad YoY =
CALCULATE(
    CALCULATE(
        [Revenue],
        SAMEPERIODLASTYEAR(Dates[Date])
    )
) - [Revenue]

// FAST: Variables + single CALCULATE
Good YoY =
VAR PYRevenue = CALCULATE([Revenue], SAMEPERIODLASTYEAR(Dates[Date]))
VAR CurrentRevenue = [Revenue]
RETURN CurrentRevenue - PYRevenue

3. Too many visuals on one page

Each visual generates one or more DAX queries. A page with 30 visuals generates 30+ queries. Optimization:

• Reduce visual count per page (target 10-15)
• Use report page tooltips instead of embedding details
• Lazy-load visuals below the fold

4. High-cardinality columns in visuals

Placing a column with millions of unique values on an axis forces the engine to process millions of groups. Use aggregated tables or limit the axis to top N values.

Tools for performance analysis

Start with Performance Analyzer (easy), go deeper with DAX Studio (advanced)

Tool	What It Shows	When to Use
Performance Analyzer	Time per visual: DAX query time, direct query time, render time	Quick check — which visuals are slow?
DAX Studio	Server Timings: SE vs FE split, query plan, row count	Deep analysis — why is this measure slow?
VertiPaq Analyzer	Model size: table sizes, column sizes, relationship sizes	Model design — which tables/columns consume the most memory?
Query Diagnostics	DirectQuery: SQL queries generated, query duration, row count	DirectQuery performance — are source queries efficient?

DAX optimization patterns

Pattern 1: Variables (always use)

// Variables prevent repeated evaluation
Revenue YoY % =
VAR Current = [Total Revenue]
VAR PY = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dates[Date]))
RETURN
    DIVIDE(Current - PY, PY)

Pattern 2: CALCULATE instead of iterator + IF

// Instead of SUMX with IF
Grocery Revenue =
CALCULATE(
    [Total Revenue],
    Products[Category] = "Grocery"
)

Pattern 3: KEEPFILTERS for intersecting filters

// KEEPFILTERS preserves existing filters instead of overriding
High Value Grocery =
CALCULATE(
    [Total Revenue],
    KEEPFILTERS(Products[Category] = "Grocery"),
    Sales[Amount] > 100
)

Pattern 4: Avoid DISTINCTCOUNT on large columns

// SLOW on high-cardinality columns
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

// FASTER: Pre-aggregate in the data layer
-- Create a customer count column in the aggregate table

💡 Scenario: Anita optimises slow dashboards

Anita at FreshCart’s sales dashboard takes 25 seconds to load. She uses Performance Analyzer and finds:

• The “Revenue by Product” matrix takes 12 seconds (shows 5,000 products)
• The “YoY Growth” card takes 8 seconds

Her fixes:

1. Product matrix: Add a Top 50 filter (reduce from 5,000 to 50 products)
2. YoY Growth: Rewrite with VAR to prevent double evaluation
3. Page design: Move 10 detail visuals to a drill-through page (reduce from 25 to 15 visuals)

Result: load time drops from 25 seconds to 3 seconds.

Question

What are the two engines that process DAX queries?

Click or press Enter to reveal answer

Answer

1. Storage Engine (SE) — reads data from VertiPaq/Direct Lake. Handles simple filters and aggregations. Multi-threaded, very fast. 2. Formula Engine (FE) — evaluates complex DAX expressions. Single-threaded, slower. Goal: push as much work to SE as possible.

Click to flip back

Question

Why is CALCULATE(SUM(), filter) faster than SUMX(table, IF())?

Click or press Enter to reveal answer

Answer

CALCULATE with a filter pushes the work to the Storage Engine (optimised for filtered aggregation). SUMX with IF forces the Formula Engine to evaluate every row individually (single-threaded). The Storage Engine path is orders of magnitude faster on large tables.

Click to flip back

Question

Name three tools for analyzing DAX performance.

Click or press Enter to reveal answer

Answer

1. Performance Analyzer (in Power BI Desktop) — shows per-visual timing. 2. DAX Studio — deep query analysis with Storage Engine vs Formula Engine split. 3. VertiPaq Analyzer — model size analysis showing table/column memory usage.

Click to flip back

Knowledge Check

Anita at FreshCart has a measure: SUMX(Sales, IF(Sales[StoreType] = 'Metro', Sales[Amount], 0)). The measure is slow on a 500M-row fact table. Which rewrite is faster?

AAVERAGEX(Sales, Sales[Amount]) with a filter on StoreTypeBCALCULATE(SUM(Sales[Amount]), Sales[StoreType] = "Metro")CFILTER(ALL(Sales), Sales[StoreType] = "Metro")DCOUNTROWS(FILTER(Sales, Sales[StoreType] = "Metro")) * AVERAGE(Sales[Amount])

Check Answer

Knowledge Check

A Power BI report page has 30 visuals and takes 45 seconds to load. Performance Analyzer shows each visual takes 1-2 seconds. What is the most effective optimization?

ARewrite all DAX measures with variablesBReduce the number of visuals per page to 10-15 and move details to drill-through pagesCSwitch all tables from Direct Lake to Import modeDAdd more Fabric capacity

Check Answer

🎬 Video coming soon

---

Next up: Incremental Refresh — refresh only what changed, keeping your semantic models fast and efficient.