Power BI April 2026 · 8 min read

Power BI DAX Optimisation — 5 Common Mistakes and How to Fix Them

Slow Power BI reports are almost always a data model or DAX problem — not a hardware problem. After reviewing hundreds of Power BI models as part of migration and audit projects, the same five DAX patterns come up again and again as the root cause of poor performance. This article explains each mistake, why it's slow, and how to fix it.

Why DAX Performance Matters

DAX (Data Analysis Expressions) is evaluated by the Vertipaq engine in Power BI's in-memory columnar database. When DAX is written well, the engine can leverage its native column-store optimisations and deliver sub-second query times even on large datasets. When it's written poorly, the engine is forced into expensive row-by-row iteration, and performance degrades rapidly as data volume grows.

The mistakes below are all patterns that prevent the Vertipaq engine from working efficiently. Fixing them typically yields 3–10× query speed improvements without changing any infrastructure.

Tool tip: Before optimising, measure. Use DAX Studio (free) to capture query plans and identify which measures are slowest. VertiPaq Analyzer will show you table and column cardinality, which predicts where compression will suffer. Never optimise blind.

The 5 Most Common DAX Mistakes

// MISTAKE 01

Using CALCULATE with Row Context Instead of SUMX

One of the most common patterns in migrated models is using CALCULATE incorrectly when iterating over rows. A common example: trying to sum a value that depends on a per-row calculation using CALCULATE inside a filtered expression, when SUMX is the right tool.

❌ Slow — forces row context iteration incorrectly

    Gross Margin % =
CALCULATE(
    DIVIDE(SUM(Sales[Revenue]) - SUM(Sales[Cost]), SUM(Sales[Revenue])),
    FILTER(Sales, Sales[Category] = "A")
)
  

✓ Fast — correct use of filter context

    Gross Margin % =
VAR TotalRevenue = CALCULATE(SUM(Sales[Revenue]), Sales[Category] = "A")
VAR TotalCost   = CALCULATE(SUM(Sales[Cost]),    Sales[Category] = "A")
RETURN DIVIDE(TotalRevenue - TotalCost, TotalRevenue)
  

Using VAR to materialise intermediate results reduces repeated evaluation and lets the engine optimise each calculation independently.

// MISTAKE 02

FILTER on an Entire Table Instead of a Column

FILTER(Table, condition) iterates over every row in the table. FILTER(VALUES(Table[Column]), condition) iterates only over the distinct values of that column — far less work for high-cardinality tables.

❌ Slow — scans every row of the entire table

    Revenue High Value =
CALCULATE(
    SUM(Sales[Revenue]),
    FILTER(Sales, Sales[OrderValue] > 10000)
)
  

✓ Fast — uses column filter, not row iteration

    Revenue High Value =
CALCULATE(
    SUM(Sales[Revenue]),
    Sales[OrderValue] > 10000
)
  

Passing a boolean expression directly to CALCULATE (without wrapping in FILTER) allows the engine to use its column-store indexing. Reserve FILTER for situations where you genuinely need to reference multiple columns in a single row condition.

// MISTAKE 03

Calculated Columns for Values That Should Be Measures

Calculated columns materialise and store a value for every row at model refresh time. Measures calculate on the fly in query context. Using calculated columns for aggregated values — sums, counts, ratios — that should be measures inflates model size and slows refresh without providing any query-time benefit.

Common offenders in migrated models: "Revenue YTD" as a calculated column, "Days Since Last Order" as a calculated column updated at refresh, percentage columns that recalculate the same formula stored in every row.

The rule: if the value changes based on filter context (slicers, page filters, cross-filtering), it should be a measure. Calculated columns are only appropriate for values that are static properties of a row — a product tier, a bucketed category, a lookup that can't be handled via relationship.

// MISTAKE 04

Many-to-Many Relationships via Bidirectional Filters

Bidirectional (both-direction) relationships feel convenient but are one of the most common causes of slow reports and incorrect aggregations in complex models. They cause filter context to propagate in unexpected directions, force the engine to evaluate ambiguous paths, and can produce subtly wrong numbers — the worst kind of bug, because it's not obvious.

The correct solution for many-to-many relationships is a bridge table with single-direction filters and explicit use of CROSSFILTER in measures where bidirectional filtering is genuinely needed. This gives you precise control over when cross-filter propagation happens, rather than allowing it everywhere.

Key indicator: If your model has bidirectional relationships and you're seeing unexpected aggregation totals — especially when filters from one table seem to affect another unexpectedly — this is almost certainly the cause. Audit all your relationships and make bidirectional the exception, not the default.

// MISTAKE 05

High-Cardinality Columns in Fact Tables

The Vertipaq engine compresses data column by column. Columns with low cardinality (few distinct values) compress extremely well; high-cardinality columns compress poorly and consume significantly more memory. The most common culprit in migrated models: text columns like free-form descriptions, notes fields, and UUIDs/GUIDs stored as text in fact tables.

Best practices to reduce cardinality:

Move descriptive text columns out of fact tables into dimension tables, and join on integer keys
Replace GUID/UUID columns with integer surrogate keys where possible
Avoid storing calculated text strings in fact tables — compute them in DAX at query time or store them in dimension tables
Review datetime columns — storing full timestamp precision in a fact table (millisecond-level) creates very high cardinality; date-only keys joined to a Date dimension are far more efficient

The Impact of Fixing These Issues

In most models we've reviewed as part of migration projects, addressing these five patterns alone reduces query times by 60–80%. A report that was loading in 8–12 seconds typically drops to under 2 seconds. The changes required are almost always in the data model and DAX — not in the report visuals or the underlying infrastructure.

If your Power BI reports are slow, start with these five checks before considering capacity upgrades or report redesign.

Slow Power BI reports in your organisation?

Fusion Data Partners offers Power BI model audits — we review your semantic model, identify performance bottlenecks, and deliver a prioritised set of optimisations with before/after benchmarks.

Book a Free Consultation →