power-bi-dax

Power BI DAX

DAX fails quietly: a measure that returns plausible numbers under one slicer combination and wrong ones under another, or a report that takes 20 seconds because one measure iterates a fact table row by row. This skill produces measures that are correct under every filter context and fast in VertiPaq, and gives a procedure for diagnosing the ones that are not.

Operating procedure

Step 1: Gather inputs

1. The model schema: fact and dimension tables, relationships and their directions, and row counts on the facts.
2. Whether a dedicated Date table exists, is marked as a date table, and covers a contiguous range - time intelligence silently misbehaves without all three.
3. The business definition of each measure in one sentence, including what it should do under totals and slicers ("margin % weighted by revenue, respecting the category slicer").
4. Performance symptoms if any: which visual, which page, how slow. Default target: any single visual renders in under 1 second; anything over 3 seconds is a defect to diagnose, not a fact of life.

Step 2: Choose measure vs calculated column

Prefer measures. They evaluate in the report's filter context and do not bloat the model. Use calculated columns only when you need a row-level value for slicing or relationships. Every calculated column is materialized and compressed into the model; every aggregation belongs in a measure.

Step 3: Write the measure with context in mind

Two contexts exist: filter context (slicers, rows, columns, CALCULATE) and row context (calculated columns, iterators like SUMX). CALCULATE is the only function that turns row context into filter context via context transition. A measure referenced inside SUMX is wrapped in an implicit CALCULATE - that context transition per row is both a correctness feature and a performance trap.

Base measure:

Total Sales = SUM ( Sales[Amount] )

Use variables for clarity and speed - evaluated once and reused:

Margin % =
VAR Revenue = [Total Sales]
VAR Cost = SUM ( Sales[Cost] )
RETURN DIVIDE ( Revenue - Cost, Revenue )

Always use DIVIDE instead of / to avoid divide-by-zero errors.

Step 4: Time intelligence on a marked date table

Sales YTD =
CALCULATE ( [Total Sales], DATESYTD ( 'Date'[Date] ) )

Sales PY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Sales YoY % =
DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )

If a time-intelligence measure returns blanks or wrong periods, check the three date-table conditions from Step 1 before touching the DAX - that is the cause in the large majority of cases.

Step 5: Control filter context deliberately

% of Total =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALLSELECTED ( Product[Category] ) )
)

• ALL removes all filters from a column or table.
• ALLSELECTED respects outer slicers but ignores inner row/column grouping.
• KEEPFILTERS intersects rather than overrides a filter inside CALCULATE.
• REMOVEFILTERS is the modern alias for clearing filters.

Pick by asking: should the denominator move when the user slices? ALLSELECTED if yes, ALL if never.

Step 6: Diagnose performance

Run Performance Analyzer, then copy the slow query into DAX Studio with server timings on. Read the split: the storage engine (VertiPaq scans) should dominate. When the formula engine consumes more than ~50% of query time, the measure is doing row-by-row work the storage engine cannot compress - rewrite it. Model-side levers, in order of payoff:

• Reduce cardinality of columns used in relationships; split datetime into separate date and time columns. A column with over ~1 million distinct values is a compression red flag.
• Prefer integer keys over strings.
• Avoid bidirectional relationships unless genuinely required; they multiply scan work and create ambiguity.
• Avoid iterators over large fact tables (10M+ rows) that reference measures per row; restate the logic as column arithmetic inside one SUMX, or pre-aggregate.
• Use SELECTEDVALUE instead of HASONEVALUE + VALUES patterns.

Worked artifact: bad/good measure pair

Business ask: sales for red products, respecting all other slicers.

Bad - iterates the entire fact table in the formula engine and forces a context transition per row:

Red Sales (Bad) =
CALCULATE (
    [Total Sales],
    FILTER ( Sales, RELATED ( Product[Color] ) = "Red" )
)

Good - a column predicate VertiPaq resolves as a compressed scan:

Red Sales (Good) =
CALCULATE ( [Total Sales], KEEPFILTERS ( Product[Color] = "Red" ) )

On a 50M-row fact table the bad version commonly runs seconds in the formula engine; the good version runs tens of milliseconds in the storage engine. Same numbers, two orders of magnitude apart. (KEEPFILTERS preserves an existing user filter on Color; drop it only if the measure must override the slicer.)

Second pair - weighted math. Bad: a calculated column Sales[Margin %] per row, then averaged in the visual - wrong weighting (simple average of row percentages) and model bloat. Good:

Weighted Margin =
SUMX ( Sales, Sales[Qty] * ( Sales[Price] - Sales[Cost] ) )

Row-by-row math belongs in an iterator inside a measure, using column arithmetic rather than nested measure references.

Deliverable

Produce a measure set where each measure has: the DAX, a one-sentence business definition, the expected behavior under totals and slicers, and - for any measure flagged slow - the DAX Studio storage/formula engine split before and after the rewrite.

Do NOT

• Do not use calculated columns for aggregations that belong in measures; the model pays in size and refresh time forever.
• Do not filter whole tables inside CALCULATE when a column predicate works; FILTER ( Sales, ... ) forces formula-engine iteration.
• Do not average percentage columns in visuals; compute ratios as measures so weighting is correct at every level.
• Do not build time intelligence without a marked, contiguous Date table; the failure is silent wrong numbers, not an error.
• Do not reference measures inside iterators over large tables without checking the timing split; implicit context transition per row is the classic hidden cost.

Quality bar

• Every measure verified at the total level and under at least two slicer combinations, not just the detail rows.
• No division uses /; all use DIVIDE.
• Date table is marked, contiguous, and every time-intelligence measure filters through it.
• No visual over 1 second without a documented diagnosis; formula engine share under ~50% on the measures that matter.
• Model has no unexplained bidirectional relationships and no string keys on large relationships.