Writing DAX Measures

❌ Naked CALCULATE without filters
CALCULATE([Sales])  -- Does nothing useful

❌ Division operator / (no error handling)
[Sales] / [Quantity]  -- Fails on zero

❌ FILTER on fact tables with scalar comparisons
FILTER(Sales, Sales[Amount] > 100)  -- Slow on large tables

❌ Filtering entire tables instead of columns
CALCULATE([Sales], FILTER(Products, ...))  -- Very slow

❌ Measures that never return BLANK
IF(ISBLANK([Sales]), 0, [Sales])  -- Hurts compression

❌ IF conditions inside iterators
SUMX(Sales, IF(Sales[Category] = "A", Sales[Amount], 0))  -- Use CALCULATE

❌ BAD - Filters entire table
CALCULATE([Sales], FILTER(ALL(Products), Products[Category] = "Electronics"))

✅ GOOD - Filters column directly
CALCULATE([Sales], Products[Category] = "Electronics")

❌ BAD - Complex logic first, then filter
VAR Result = [ComplexCalculation]
RETURN CALCULATE(Result, 'Date'[Year] = 2024)

✅ GOOD - Filter first, then calculate
CALCULATE([ComplexCalculation], 'Date'[Year] = 2024)

✅ Use VAR to:
- Avoid repetitive calculations (computed only once)
- Improve code readability
- Replace EARLIER pattern (deprecated)
- Store intermediate results
- Optimize IF conditions
- Cache measure values outside iterators

Total Sales Ratio =
VAR TotalSales = SUM(Sales[Amount])
VAR AllSales = CALCULATE(TotalSales, ALL(Sales))
RETURN
    DIVIDE(TotalSales, AllSales)

❌ BAD - Iterator with IF condition
SUMX(Sales, IF(Sales[Amount] > 100, Sales[Amount], 0))

✅ GOOD - Filter first, then iterate
SUMX(FILTER(Sales, Sales[Amount] > 100), Sales[Amount])

✅ BETTER - Use CALCULATE if possible
CALCULATE(SUM(Sales[Amount]), Sales[Amount] > 100)

✅ Use SELECTEDVALUE() instead of VALUES()
-- VALUES() errors on multiple values
-- SELECTEDVALUE() returns BLANK on multiple values

✅ Be explicit about context
CALCULATE([Measure], FILTER(...))  -- Clear intent

✅ Cache measure values outside iterators
VAR MeasureValue = [Measure]
RETURN SUMX(Table, MeasureValue * Table[Column])

# Year-to-Date
Sales YTD =
CALCULATE(
    [Total Sales],
    DATESYTD('Date'[Date])
)

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

# Year-over-Year Growth %
YoY Growth % =
VAR CurrentYear = [Total Sales]
VAR PreviousYear = [Sales PY]
RETURN
    DIVIDE(CurrentYear - PreviousYear, PreviousYear)

# Month-to-Date
Sales MTD =
CALCULATE(
    [Total Sales],
    DATESMTD('Date'[Date])
)

# Quarter-to-Date
Sales QTD =
CALCULATE(
    [Total Sales],
    DATESQTD('Date'[Date])
)

# Grand Total (ignores ALL filters)
Grand Total Sales =
CALCULATE([Total Sales], ALL(Sales))

# Total Respecting Slicers (ignores only visual filters)
Dynamic Total =
CALCULATE([Total Sales], ALLSELECTED(Sales))

# Total by Region (keeps Region filter, removes others)
Total by Region =
CALCULATE([Total Sales], ALLEXCEPT(Sales, Sales[Region]))

# Clear specific filter (recommended syntax)
Sales Without Date Filter =
CALCULATE([Total Sales], REMOVEFILTERS('Date'))

✅ Let measures return BLANK naturally
-- Don't replace BLANK with 0 unless required
-- BLANK enables VertiPaq compression optimization

✅ Use DIVIDE() for safe division
DIVIDE([Numerator], [Denominator], 0)  -- Returns 0 on division by zero

✅ Use COALESCE() for first non-blank value
COALESCE([Measure1], [Measure2], [Measure3], 0)

✅ Use ISBLANK() to check before calculations
IF(ISBLANK([Measure]), 0, [Measure])  -- Only when UI requires no blanks

❌ Never create measures that never return BLANK
-- Kills VertiPaq compression and performance

✅ Measures:      [Total Sales]
✅ Columns:       Products[Category]
✅ Tables:        Sales (be consistent: singular OR plural)
✅ Clear names:   [Year-over-Year Growth %]
❌ Abbreviations: [YoY_Grth_Pct]

✅ Add comments for complex logic
-- Calculate active customer sales only
-- Customers with purchases in last 90 days

✅ Use descriptive variable names
VAR ActiveCustomers = ...  -- Not just "AC"

✅ Format code for readability
-- Use DAX Formatter (daxformatter.com)

□ All functions exist in DAX (no Excel/SQL functions)
□ All tables/columns match live schema exactly (case-sensitive)
□ No deprecated functions (EARLIER, IFERROR, ISERROR, FIRSTNONBLANK, LASTNONBLANK)
□ Context transitions are explicit and optimized
□ Variables used for repetitive calculations
□ DIVIDE() used instead of / operator
□ Filters applied to columns, not tables
□ Time intelligence functions only in filter arguments of CALCULATE
□ Measure naming follows convention: [Measure Name]
□ Comments added for complex logic
□ Code formatted for readability