dax-mastery

What this skill does

# DAX (Data Analysis Expressions) Mastery

## Overview

Complete DAX reference covering evaluation contexts, CALCULATE, time intelligence, iterators, table functions, performance optimization, and advanced patterns. DAX is the formula language for Power BI measures, calculated columns, calculated tables, and RLS filters.

## Evaluation Contexts

### Row Context
- Created by: Calculated columns, iterators (SUMX, FILTER, AVERAGEX, etc.), row-by-row evaluation
- Each row in the table has its own row context
- Access columns directly: `Sales[Amount]`
- Nested iterators create nested row contexts

### Filter Context
- Created by: Slicers, visual filters, page filters, report filters, CALCULATE arguments
- Determines which rows are visible to aggregation functions
- Does NOT provide row-level access (cannot use `Sales[Amount]` directly in a measure without aggregation)

### Context Transition
- CALCULATE converts row context into filter context
- Happens when a measure is referenced inside an iterator
- Each row's column values become filter arguments

```dax
// Context transition example:
Sales Amount = SUM(Sales[Amount])

// Inside SUMX, each row triggers context transition:
Weighted Amount =
SUMX(
    Products,
    Products[Weight] * [Sales Amount]  // [Sales Amount] triggers CALCULATE internally
)
```

## CALCULATE - The Most Important Function

```dax
CALCULATE(<expression>, <filter1>, <filter2>, ...)
```

**Filter argument types:**

| Type | Example | Behavior |
|------|---------|----------|
| Boolean (table filter) | `Products[Color] = "Red"` | Adds filter, keeps existing context |
| Table expression | `FILTER(ALL(Products), Products[Price] > 100)` | Replaces filter on affected columns |
| REMOVEFILTERS | `REMOVEFILTERS(Products[Color])` | Removes existing filter on column |
| ALL | `ALL(Products)` | Removes all filters on table |
| KEEPFILTERS | `KEEPFILTERS(Products[Color] = "Red")` | Intersects with existing filter |
| USERELATIONSHIP | `USERELATIONSHIP(Sales[ShipDate], Date[Date])` | Activates inactive relationship |
| CROSSFILTER | `CROSSFILTER(Sales[ProductID], Products[ID], Both)` | Changes cross-filter direction |

**Critical rules:**
- Boolean filters are syntactic sugar for FILTER(ALL(column), condition)
- Boolean filters REPLACE the existing filter on that column
- Use KEEPFILTERS to ADD to (intersect with) existing filters
- CALCULATE modifiers (ALL, REMOVEFILTERS) execute BEFORE filter arguments

## Time Intelligence Quick Reference

**Prerequisite:** A proper Date table marked as a date table with a continuous date column.

| Function | Purpose | Example |
|----------|---------|---------|
| TOTALYTD | Year-to-date | `TOTALYTD([Sales], Date[Date])` |
| TOTALMTD | Month-to-date | `TOTALMTD([Sales], Date[Date])` |
| TOTALQTD | Quarter-to-date | `TOTALQTD([Sales], Date[Date])` |
| SAMEPERIODLASTYEAR | Same period, prior year | `CALCULATE([Sales], SAMEPERIODLASTYEAR(Date[Date]))` |
| DATEADD | Shift by interval | `CALCULATE([Sales], DATEADD(Date[Date], -1, MONTH))` |
| PARALLELPERIOD | Entire shifted period | `CALCULATE([Sales], PARALLELPERIOD(Date[Date], -1, QUARTER))` |
| DATESYTD | Date table filtered to YTD | `CALCULATE([Sales], DATESYTD(Date[Date]))` |
| DATESBETWEEN | Date range | `CALCULATE([Sales], DATESBETWEEN(Date[Date], start, end))` |
| PREVIOUSMONTH | Entire previous month | `CALCULATE([Sales], PREVIOUSMONTH(Date[Date]))` |
| PREVIOUSYEAR | Entire previous year | `CALCULATE([Sales], PREVIOUSYEAR(Date[Date]))` |

**Common time intelligence patterns:**

```dax
// Year-over-Year Growth %
YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorYearSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date]))
RETURN
    DIVIDE(CurrentSales - PriorYearSales, PriorYearSales)

// Rolling 12-Month Total
Rolling 12M =
CALCULATE(
    [Total Sales],
    DATESINPERIOD(Date[Date], MAX(Date[Date]), -12, MONTH)
)

// Moving Average (3 months)
3M Moving Avg =
AVERAGEX(
    DATESINPERIOD(Date[Date], MAX(Date[Date]), -3, MONTH),
    CALCULATE([Total Sales])
)
```

## Variables (VAR/RETURN)

Always use variables for readability and performance:

```dax
Profit Margin % =
VAR TotalRevenue = SUM(Sales[Revenue])
VAR TotalCost = SUM(Sales[Cost])
VAR Profit = TotalRevenue - TotalCost
RETURN
    DIVIDE(Profit, TotalRevenue)
```

**Rules:**
- Variables are evaluated once (performance benefit when reused)
- Variables capture filter context at the point of definition
- Variables can hold scalar values or tables
- Use meaningful names (not `x`, `temp`)

## Iterator Functions

Iterators scan a table row by row, creating row context:

| Function | Purpose |
|----------|---------|
| SUMX | Sum of expression evaluated per row |
| AVERAGEX | Average of expression per row |
| MINX / MAXX | Min/Max of expression per row |
| COUNTX | Count of non-blank expression results |
| RANKX | Rank based on expression |
| FILTER | Returns table rows matching condition |
| ADDCOLUMNS | Adds calculated columns to table |
| SELECTCOLUMNS | Returns table with selected/calculated columns |
| GENERATE | Cross-join with row context |

```dax
// Weighted average price
Weighted Avg Price =
SUMX(
    Sales,
    Sales[Quantity] * RELATED(Products[UnitPrice])
) / SUM(Sales[Quantity])
```

## Calculation Groups

Reduce measure sprawl by defining reusable calculation patterns:

```dax
// Instead of creating YTD, PY, YoY for EVERY measure:
// Create ONE calculation group with items:
// - Current: SELECTEDMEASURE()
// - YTD: CALCULATE(SELECTEDMEASURE(), DATESYTD(Date[Date]))
// - PY: CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(Date[Date]))
// - YoY%: VAR Curr = SELECTEDMEASURE()
//         VAR PY = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(Date[Date]))
//         RETURN DIVIDE(Curr - PY, PY)
```

Create via Tabular Editor, TMDL view in Desktop, or TOM/.NET SDK.

## Field Parameters

Enable users to dynamically switch dimensions or measures in visuals:

```dax
// Created via Modeling tab > New parameter > Fields
// Generates a calculated table:
Parameter =
{
    ("Revenue", NAMEOF(Sales[Total Revenue]), 0),
    ("Profit", NAMEOF(Sales[Total Profit]), 1),
    ("Units", NAMEOF(Sales[Total Units]), 2)
}
```

## User-Defined Functions (September 2025 Preview)

The most significant DAX language update since variables (2015). Define reusable parameterized functions:

```dax
// Define a UDF in DAX query view or model
DEFINE
FUNCTION AddTax = (amount : NUMERIC) => amount * 1.1

// Nest UDFs
FUNCTION AddTaxAndDiscount = (amount : NUMERIC, discount : NUMERIC) =>
    AddTax(amount - discount)

EVALUATE { AddTaxAndDiscount(100, 20) }  // Returns 88
```

**Parameter types:** `NUMERIC`, `Scalar`, `Table`, `AnyVal`, `AnyRef`, `CalendarRef`, `ColumnRef`, `MeasureRef`, `TableRef`

**Parameter modes:** `val` (eager evaluation) or `expr` (lazy/context-sensitive)

**Usage:** Once defined and saved to the model, call UDFs from measures, calculated columns, visual calculations, and other UDFs.

**Enable:** File > Options > Preview features > DAX user-defined functions

## Window Functions (WINDOW, INDEX, OFFSET)

DAX window functions for row-relative and range calculations:

```dax
// Running total using WINDOW
Running Total =
CALCULATE(
    [Total Sales],
    WINDOW(1, ABS, 0, REL, ALLSELECTED(Date[Month]),
        ORDERBY(Date[MonthNumber], ASC))
)

// Previous row value using OFFSET
Previous Month Sales =
CALCULATE(
    [Total Sales],
    OFFSET(-1, ALLSELECTED(Date[Month]),
        ORDERBY(Date[MonthNumber], ASC))
)

// Nth row using INDEX
First Month Sales =
CALCULATE(
    [Total Sales],
    INDEX(1, ALLSELECTED(Date[Month]),
        ORDERBY(Date[MonthNumber], ASC))
)
```

**Key clauses:**
- `ORDERBY` -- sort order within the window
- `PARTITIONBY` -- subset of rows (the "window" partition)
- `MATCHBY` -- identify the current row in ambiguous contexts

## Visual Calculations (2024-2026)

Calculations scoped to the visual matrix, not the data model:

| Function | Purpose |
|----------|---------