🎯 Situation

A few weeks ago I was reviewing a Power BI report that was taking 8 to 12 seconds to load every visual. The data model was reasonable — not perfect, but not the problem. The issue was the DAX.

Nested IFs inside SUMX iterating over 2 million rows. Calculated columns doing what measures should do. FILTER materializing tables inside CALCULATE. All fixable. All patterns I see in almost every project I inherit.

👉 DAX is not Excel. The syntax looks familiar — but the execution model is completely different. Writing DAX like Excel formulas is the fastest way to build a slow, fragile report.

Here are the 10 most common mistakes — and exactly how to fix them.

⚠️ Challenge

Most people who learn Power BI come from Excel. That's a great starting point — and a dangerous mental model for DAX.

📄 Excel formulas

  • Evaluated cell by cell
  • Left to right, top to bottom
  • Immediate, familiar, predictable
  • Nested IFs are standard practice
  • Performance rarely a concern at small scale

⚡ DAX measures

  • Evaluated against a filter context
  • Executed by two separate engines (storage + formula)
  • Performance depends on how the engine can optimize
  • Iteration is expensive — avoid it when possible
  • Structure and naming matter as much as logic

The fix isn't to avoid DAX — it's to understand how it actually works. These 10 patterns will get you 80% of the way there.

🔍 The 10 Mistakes

Each error below includes a before, an after, and the reason it matters. Some are readability improvements. Some are major performance gains. All of them are worth fixing.

#1 — Infinite nested IF → use SWITCH

❌ Bad 
IF([Score] = "A", 1,
    IF([Score] = "B", 2,
        IF([Score] = "C", 3, 0)
    )
)
✅ Good 
SWITCH([Score],
    "A", 1,
    "B", 2,
    "C", 3,
    0  -- default
)
  • More readable and easier to extend
  • Faster: SWITCH short-circuits on match
  • Trivial to add a new case without nesting

#2 — ISBLANK everywhere → use COALESCE

❌ Bad 
IF(ISBLANK([Revenue]), 0, [Revenue])
✅ Good 
COALESCE([Revenue], 0)
  • Half the characters, same result
  • Handles both BLANK and NULL
  • Modern DAX standard — use it consistently

#3 — Repeating the same calculation → use VAR

❌ Bad 
IF(
    [Revenue] - [Cost] > 0,
    ([Revenue] - [Cost]) / [Revenue],
    0
)
✅ Good 
VAR Profit = [Revenue] - [Cost]
RETURN
IF(
    Profit > 0,
    DIVIDE(Profit, [Revenue]),
    0
)
  • Each expression is evaluated once — not twice
  • Reads like clean code, not a formula
  • Easier to debug: evaluate VAR by VAR

#4 — Classic division → use DIVIDE

❌ Bad 
[Profit] / [Revenue]
✅ Good 
DIVIDE([Profit], [Revenue])
  • Handles divide-by-zero automatically (returns BLANK by default)
  • Avoids silent errors in visuals
  • Accepts an optional third argument for the fallback value

#5 — Unnecessary FILTER inside CALCULATE

❌ Bad 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Region] = "Canada")
)
✅ Good 
CALCULATE(
    SUM(Sales[Amount]),
    Sales[Region] = "Canada"
)
  • FILTER materializes a full table — expensive at scale
  • Direct predicate is optimized by the VertiPaq engine
  • One of the highest-impact performance fixes in DAX

#6 — COUNTROWS(FILTER(…)) → use CALCULATE

❌ Bad 
COUNTROWS(
    FILTER(Sales, Sales[Status] = "Won")
)
✅ Good 
CALCULATE(
    COUNTROWS(Sales),
    Sales[Status] = "Won"
)
  • FILTER iterates every row before counting
  • CALCULATE pushes the filter to the storage engine
  • Same result — dramatically faster on large tables

#7 — SUMX where SUM is enough

❌ Bad 
SUMX(Sales, Sales[Amount])
✅ Good 
SUM(Sales[Amount])
  • SUMX iterates row by row (formula engine)
  • SUM runs in the storage engine — much faster
  • Reserve SUMX for row-level expressions: SUMX(Sales, Sales[Qty] * Sales[Price])

#8 — Calculated columns for dynamic aggregations

❌ Bad 
-- Calculated column (wrong approach)
Margin % = Sales[Profit] / Sales[Revenue]
✅ Good 
-- DAX measure (correct approach)
Margin % = DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]))
  • Columns are computed at refresh and stored in memory
  • Measures are computed at query time — always context-aware
  • A measure adapts to every slicer, filter, and visual automatically

#9 — ALL on a fact table — too broad

❌ Bad 
CALCULATE([Revenue], ALL(Sales))
✅ Good 
CALCULATE([Revenue], ALL(DimDate))
  • ALL(Sales) removes every filter on the fact — usually not what you want
  • Targeting a specific dimension is precise and predictable
  • Common use case: % of total ignoring date filter only

#10 — One 30-line measure with no structure

❌ Bad 
-- Everything inline, nothing named
[Revenue] - [Cost] / ([Revenue] - [Cost] + [Overhead])
✅ Good 
VAR TotalRevenue  = SUM(Sales[Revenue])
VAR TotalCost     = SUM(Sales[Cost])
VAR Overhead      = [OverheadMeasure]
VAR NetProfit     = TotalRevenue - TotalCost - Overhead

RETURN
DIVIDE(NetProfit, TotalRevenue)
  • Debuggable: comment out RETURN and replace with a VAR name
  • Self-documenting: variable names explain the logic
  • Reusable: extract any VAR into its own base measure

✓️ The 3 Real Levers

Fixing individual DAX mistakes is useful. But if you want to understand why some reports are fast and others are slow, there are only three things that matter:

  • Modeling first (80% of the problem) — a star schema, proper relationships, and clean dimension tables solve most performance issues before you write a single DAX line
  • Optimized DAX second — avoid iteration (SUMX, FILTER) where aggregation (SUM, CALCULATE) can do the job; use VAR to eliminate repeated evaluation
  • Reduce cardinality and iteration last — high-cardinality columns in fact tables, unnecessary CALCULATE nesting, and missing DIVIDE calls are the remaining bottlenecks
Power BI ≠ Excel. DAX ≠ Excel formulas. Performance = model + DAX + knowing which engine handles what. Once those three things click, your reports get faster without rewriting everything from scratch.

💡 Summary

You don't need to memorize all 10 at once. Start with the ones that apply to your current report and fix them one by one. The gains compound.

The three highest-ROI fixes:

  • Replace FILTER with direct predicates in CALCULATE — immediate performance gain on any large table
  • Switch to VAR + RETURN in any measure with repeated expressions — readability and performance in one move
  • Replace SUMX with SUM wherever there's no row-level expression — forces you to think about when iteration is actually needed

DAX rewards structure. The more intentional you are about what you write — and why — the faster and more maintainable your reports become.

👉 A slow Power BI report is almost never a Power BI problem.

It's a DAX problem. And DAX problems have patterns.