Writes correct, fast DAX measures with proper filter context, time intelligence on a marked date table, and VertiPaq-friendly patterns, diagnosing slow visuals down to the storage-vs-formula engine split. Use when someone asks "why does my DAX measure return the wrong total", "write a YTD or year-over-year measure", "my Power BI report is slow", "CALCULATE isn't doing what I expect", or "measure vs calculated column". Do NOT use for Tableau calculated fields and dashboard design - use tableau-best-practices instead; for writing warehouse SQL and turning query results into findings - use sql-to-insights instead; for Excel or Google Sheets financial models - use spreadsheet-model-builder instead.
Click to play with sound.
---
name: Power BI DAX
description: Writes correct, fast DAX measures with proper filter context, time intelligence on a marked date table, and VertiPaq-friendly patterns, diagnosing slow visuals down to the storage-vs-formula engine split. Use when someone asks "why does my DAX measure return the wrong total", "write a YTD or year-over-year measure", "my Power BI report is slow", "CALCULATE isn't doing what I expect", or "measure vs calculated column". Do NOT use for Tableau calculated fields and dashboard design - use tableau-best-practices instead; for writing warehouse SQL and turning query results into findings - use sql-to-insights instead; for Excel or Google Sheets financial models - use spreadsheet-model-builder instead.
---
# 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.