DAX rules and anti-patterns

DAX is a small, weird, powerful language. A handful of rules cover the majority of problem reports you will see in production.

The five rules

1. Measures, not calculated columns

Calculated columns materialize per row and bloat the in-memory model. Measures compute only for the visible grain.

// Wrong: calculated column — one row per fact, forever in RAM
Revenue USD = 'fct_orders'[amount] * 'fct_orders'[fx_rate]

// Right: measure — computes only for visible visuals
Revenue USD =
    SUMX('fct_orders', 'fct_orders'[amount] * 'fct_orders'[fx_rate])

If you need a derived column to join on or group by, push it upstream to a dbt model or Databricks view. It compresses better and your model stays narrow.

2. Filter before you iterate

CALCULATE(SUM(x), filter) is faster than SUMX(FILTER(table, predicate), x). The first pushes the filter to the storage engine; the second materializes the filtered table in memory first.

// Wrong: materializes the filtered table
Completed Revenue =
    SUMX(
        FILTER('fct_orders', 'fct_orders'[status] = "completed"),
        'fct_orders'[amount_usd]
    )

// Right: pushes filter to storage engine
Completed Revenue =
    CALCULATE(
        SUM('fct_orders'[amount_usd]),
        'fct_orders'[status] = "completed"
    )

Rule of thumb: if you can express what you need with CALCULATE + filters, do it that way. Reach for SUMX(FILTER(...)) only when the iteration itself needs row context.

3. DIVIDE(), not /

DIVIDE(a, b) returns blank on divide-by-zero; a / b returns infinity or an error. Blank is visually clean; infinity breaks charts.

// Wrong: errors or infinities on zero denominators
Conversion Rate = [Completed Orders] / [Total Orders]

// Right: blank on zero
Conversion Rate = DIVIDE([Completed Orders], [Total Orders])

DIVIDE() is also marginally faster because the storage engine handles the zero check once; the explicit IF version re-evaluates the denominator.

4. Variables for anything reused

VAR x = ... evaluates once. Re-referencing the expression in RETURN does not recompute.

// Wrong: evaluates [Total Revenue] twice
Revenue YoY =
    DIVIDE(
        [Total Revenue] - CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('dim_date'[date])),
        CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('dim_date'[date]))
    )

// Right: each expression evaluated once
Revenue YoY =
    VAR Current = [Total Revenue]
    VAR Prior   = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('dim_date'[date]))
    RETURN
        DIVIDE(Current - Prior, Prior)

Variables also make measures readable, which matters for review and debugging.

5. No bidirectional relationships

Bidirectional cross-filtering is a query-plan explosion. The engine has to consider paths in both directions for every measure that touches the relationship.

Instead, use CROSSFILTER() inside a specific measure when you need bidirectional behavior locally:

// Single-direction relationship in the model
// Bidirectional only inside this specific measure:
Customer Count =
    CALCULATE(
        DISTINCTCOUNT('dim_customers'[customer_id]),
        CROSSFILTER('fct_orders'[customer_id], 'dim_customers'[customer_id], Both)
    )

The query plan stays narrow for every other measure.

Anti-patterns catalog

IFERROR wrapping everything

// Wrong: masks real issues; expensive to evaluate
Safe Revenue = IFERROR(SUM('fct_orders'[amount_usd]), 0)

IFERROR hides genuine errors that should surface. If you are wrapping to mask divide-by-zero, use DIVIDE(); if you are wrapping because an upstream field is sometimes bad, fix the source.

SWITCH(TRUE(), ...) chain for logic

// Works but grows slow with every new branch
Band =
    SWITCH(TRUE(),
        [Revenue] < 1000,      "Small",
        [Revenue] < 10000,     "Medium",
        [Revenue] < 100000,    "Large",
        "Enterprise"
    )

For a handful of branches this is fine. For more than six or seven, push the bands into a dimension table and join; the engine optimizes CALCULATE + equality better than long SWITCH chains.

Double iteration

// Wrong: iterates over the same table twice
Total =
    SUMX('fct_orders', 'fct_orders'[amount]) +
    SUMX('fct_orders', 'fct_orders'[tax])

// Right: one iteration
Total =
    SUMX('fct_orders', 'fct_orders'[amount] + 'fct_orders'[tax])

RELATED on the many side

RELATED looks up a value on the "one" side of a relationship from the "many" side. The reverse (RELATEDTABLE) is much more expensive; avoid iterating from the one side into the many side when a measure on the many side gives the same answer.

Hidden calculated columns

Creating a calculated column and hiding it "because it's not for end users" is still a calculated column. It still materializes per row, still bloats the model. Hide implies "do not show"; hiding does not mean "does not cost anything".

EARLIER / EARLIEST

These exist for row-context-to-row-context traversal. They worked in 2010; VAR is cleaner for everything they do. Do not write EARLIER in a measure authored in 2026.

Measure formatting

Every shared-model measure carries a FormatString. Default the format in TMDL so report authors do not override per visual.

DAX Studio: the review tool

Every measure change to a shared semantic model should pass through DAX Studio before it ships:

• Server Timings: breaks execution into Formula Engine (FE) and Storage Engine (SE) time. High FE is usually a row-context issue; high SE is usually a scan-too-much issue.
• Query Plan: the actual plan Power BI builds. Unexpected operators (nested loops, full scans) flag issues.
• Run same query repeatedly: confirm caching behavior.

Best Practice Analyzer rules

Tabular Editor's BPA ships a strong default set. The Causeway-specific additions:

• Every measure on a Certified model has a description.
• No calculated columns on fact tables (banned outright; hide-and-rename is not sufficient).
• No bidirectional relationships without a documented waiver comment.
• No DIVIDE() with only two arguments in measures that can produce zero denominators (third-arg blank is implicit but should be explicit in code review).
• Measure names use Title Case with spaces; no snake_case measures (snake_case is for columns, measures are user-facing).

Ship BPA as a CI gate; see CI/CD for Power BI.

See also

• DirectQuery tuning — where DAX performance matters most.
• Semantic models — the artifact DAX measures live in.
• Model authoring standards — the full rule set for a shared model.