Power BI DAX Pattern Library

Chapter 1

DAX Best Practices Guide

Field-tested rules for writing maintainable, performant DAX measures.

Measure Organization

Naming Convention

Type	Pattern	Example
Base measure	`Total {Noun}`	`Total Revenue`, `Total Orders`
Rate/Percentage	`{Noun} %` or `{Noun} Rate`	`Conversion Rate`, `Margin %`
Comparison	`{Metric} {Period}`	`Revenue Prior Year`, `Revenue YTD`
Growth	`{Metric} {Period} Growth`	`Revenue YoY Growth`
Conditional	`{Metric} ({Condition})`	`Revenue (New Customers)`
Helper/Hidden	Prefix with `_`	`_Filtered Revenue Base`

Display Folders

Organize measures into folders in the model:

Revenue/
├── Total Revenue
├── Revenue YTD
├── Revenue MTD
├── Revenue Prior Year
└── Revenue YoY Growth

Customers/
├── Unique Customers
├── New Customers
├── Returning Customers
└── Customer Retention Rate

Targets/
├── Budget Amount
├── Variance to Budget
├── Budget Attainment %
└── Budget Status

Performance Rules

1. Variables Prevent Repeated Calculation

dax

-- BAD: Calculates [Total Revenue] twice
Growth % =
DIVIDE(
    [Total Revenue] - [Revenue SPLY],
    [Revenue SPLY]
)

-- GOOD: Calculate once, use twice
Growth % =
VAR CurrentRevenue = [Total Revenue]
VAR PriorRevenue = [Revenue SPLY]
RETURN
    DIVIDE(CurrentRevenue - PriorRevenue, PriorRevenue, BLANK())

2. DIVIDE Instead of Division Operator

dax

-- BAD: Will error on divide-by-zero
Margin = ([Revenue] - [Cost]) / [Revenue]

-- GOOD: Returns BLANK() when denominator is zero
Margin = DIVIDE([Revenue] - [Cost], [Revenue], BLANK())

3. Avoid Iterating Large Tables Unnecessarily

dax

-- BAD: SUMX iterates every row even if SUM would work
Total Revenue = SUMX('Sales', 'Sales'[Amount])

-- GOOD: SUM is optimized for single-column aggregation
Total Revenue = SUM('Sales'[Amount])

-- USE SUMX ONLY WHEN: you need row-level calculation
Total Revenue =
SUMX('Sales', 'Sales'[Quantity] * 'Sales'[Unit Price] * (1 - 'Sales'[Discount]))

4. DISTINCTCOUNT vs COUNTROWS(VALUES(...))

dax

-- GOOD: Optimized for distinct counting
Unique Customers = DISTINCTCOUNT('Orders'[Customer ID])

-- AVOID: Creates intermediate table
Unique Customers = COUNTROWS(VALUES('Orders'[Customer ID]))
-- Only use VALUES() when you need the actual table for further operations

5. Filter Context Awareness

dax

-- Know what ALL, ALLEXCEPT, ALLSELECTED do:
-- ALL() = Removes ALL filters on the table/column
-- ALLSELECTED() = Removes filters from the visual, keeps slicer filters
-- ALLEXCEPT() = Removes all filters EXCEPT specified columns
-- REMOVEFILTERS() = Same as ALL() but reads better in context

-- Use ALLSELECTED for "percentage of visible total"
Share of Visible Total =
DIVIDE([Total Revenue], CALCULATE([Total Revenue], ALLSELECTED('Products')))

Common Mistakes

Mistake 1: Circular Dependency

dax

-- FAILS: A references B, B references A
Measure A = [Measure B] + 1
Measure B = [Measure A] - 1

-- FIX: Extract shared logic into a base measure
_Base = SUM('Table'[Amount])
Measure A = [_Base] + 1
Measure B = [_Base] - 1

Mistake 2: Using CALCULATE When Not Needed

dax

-- UNNECESSARY: CALCULATE with no filter modification
Revenue = CALCULATE(SUM('Sales'[Amount]))

-- CLEANER: Direct aggregation
Revenue = SUM('Sales'[Amount])

Mistake 3: Ignoring BLANK Propagation

dax

-- PROBLEM: Subtraction with BLANK produces BLANK
Change = [Revenue This Year] - [Revenue Last Year]
-- If Last Year is BLANK (new product), Change is BLANK too

-- FIX: Handle BLANK explicitly
Change =
VAR Prior = IF(ISBLANK([Revenue Last Year]), 0, [Revenue Last Year])
RETURN
    [Revenue This Year] - Prior

Mistake 4: Not Using TREATAS for Virtual Relationships

dax

-- If you need to filter one table by values from an unrelated table:
Revenue for Budget Products =
CALCULATE(
    [Total Revenue],
    TREATAS(VALUES('Budget'[Product ID]), 'Sales'[Product ID])
)

Date Table Requirements

Every Power BI model with time intelligence NEEDS a proper date table:

1. Contiguous dates — No gaps from earliest to latest date in your data

2. Mark as Date Table — Right-click → Mark as Date Table → select date column

3. Minimum columns: Date, Year, Quarter, Month, Month Name, Day of Week

4. Fiscal calendar columns if you use fiscal periods

5. Relationship: One-to-many from Calendar[Date] to Fact[Date]

dax

-- Creating a Date Table in DAX (if not available from source)
Calendar =
VAR MinDate = MIN('Orders'[Order Date])
VAR MaxDate = MAX('Orders'[Order Date])
RETURN
    ADDCOLUMNS(
        CALENDAR(DATE(YEAR(MinDate), 1, 1), DATE(YEAR(MaxDate), 12, 31)),
        "Year", YEAR([Date]),
        "Quarter", "Q" & FORMAT([Date], "Q"),
        "Month", MONTH([Date]),
        "Month Name", FORMAT([Date], "MMMM"),
        "Year-Month", FORMAT([Date], "YYYY-MM"),
        "Day of Week", WEEKDAY([Date]),
        "Day Name", FORMAT([Date], "dddd"),
        "Is Weekend", WEEKDAY([Date]) IN {1, 7}
    )

Debugging Techniques

Technique 1: Decompose with Variables

When a complex measure returns wrong results, break it into VAR steps and return each one individually to find the problematic step.

Technique 2: Use COUNTROWS to Check Filter Context

dax

Debug Row Count =
COUNTROWS('Orders') & " rows in context"

Technique 3: Check What Filters Are Active

dax

Debug Filters =
CONCATENATEX(
    FILTERS('Products'[Category]),
    'Products'[Category],
    ", "
)

Technique 4: DAX Studio for Performance

Use DAX Studio to profile query performance
Look at Storage Engine (SE) vs Formula Engine (FE) time
SE queries are fast (columnar scan); FE is slow (row-by-row)
Goal: Minimize FE time by simplifying measure logic

Power BI DAX Pattern Library v1.0.0 — Free Preview

Contents