dax-mastery（掌握达克斯）

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

// 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

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

Filter argument types:

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.

Common time intelligence patterns:

// 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:

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:

// 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:

// 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:

// 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:

// 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:

// 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:

Visual calculations are defined per-visual and do not affect the semantic model.

Calendar-Based Time Intelligence (September 2025 Preview)

Define custom calendars (fiscal, retail, 13-month, lunar) with 8 new week-based functions:

Enable: File > Options > Preview features > Enhanced DAX Time Intelligence

Dynamic Format Strings

Apply context-dependent formatting without converting to text (GA in Desktop and Report Server Jan 2025+):

// Dynamic format string for currency
Total Sales =
SUM(Sales[Amount])

// Format string expression (set in measure properties):
// = IF(SELECTEDVALUE(Currency[Code]) = "EUR", "€#,##0.00", "$#,##0.00")

Advantage over FORMAT(): Keeps numeric data type, enabling correct chart rendering and sorting.

TABLEOF and NAMEOF (February 2026)

Reference model objects that auto-adapt to renames:

// NAMEOF returns the name of a column/measure/calendar as text
NAMEOF(Sales[Amount])  // Returns "Amount"

// TABLEOF returns a reference to the table of a column/measure
TABLEOF(Sales[Amount])  // Returns reference to Sales table

Useful inside UDFs for safer, rename-proof code.

Common Anti-Patterns

Additional Resources

Reference Files

• references/dax-function-categories.md -- Complete function reference organized by category including INFO functions, window functions, and 2025-2026 additions
• references/dax-patterns-advanced.md -- Advanced patterns: virtual relationships, dynamic segmentation, parent-child hierarchies, basket analysis