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

DAX Function Categories Reference

Aggregation Functions

Iterator (X) Functions

Filter Functions

Table Functions

Time Intelligence Functions

Logical Functions

Text Functions

Math and Statistical Functions

Relationship Functions

Window Functions (2023+)

Visual Calculation Functions (2024-2026)

New Functions (2025-2026)

Statistical Functions

Information Functions

INFO DAX Functions (Model Metadata)

INFO functions return metadata about the semantic model as tables. They replace DMVs with native DAX capability.

INFO.VIEW Functions (Usable in calculated tables, columns, measures, and DAX queries)

INFO Functions (DAX query view only, require semantic model admin permissions)

Example -- self-documenting model:

// Create a calculated table that lists all measures
EVALUATE
ADDCOLUMNS(
    SELECTCOLUMNS(
        INFO.VIEW.MEASURES(),
        "Measure", [Name],
        [Description],
        "DAX Formula", [Expression],
        "State", [State]
    ),
    "Model name", "My Semantic Model",
    "As of date", NOW()
)

Advanced DAX Patterns

1. Virtual Relationships with TREATAS

Use TREATAS to create virtual relationships without physical model relationships:

Sales by Budget Category =
CALCULATE(
    [Total Sales],
    TREATAS(
        VALUES(Budget[CategoryID]),
        Sales[CategoryID]
    )
)

When to use: Connecting tables that share a key but should not have a physical relationship (e.g., budget vs actual from different sources).

2. Dynamic Segmentation

Create dynamic segmentation without adding columns to the model:

// Step 1: Create a disconnected segmentation table
// (using DATATABLE or a calculated table)
Segments = DATATABLE(
    "Segment", STRING, "Min", INTEGER, "Max", INTEGER,
    {
        {"Low", 0, 100},
        {"Medium", 100, 500},
        {"High", 500, 10000}
    }
)

// Step 2: Measure using the segmentation
Sales by Segment =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL(Sales[Amount]),
        VAR CurrentAmount = Sales[Amount]
        VAR SegMin = SELECTEDVALUE(Segments[Min])
        VAR SegMax = SELECTEDVALUE(Segments[Max])
        RETURN CurrentAmount >= SegMin && CurrentAmount < SegMax
    )
)

3. Parent-Child Hierarchy (Unary Operator)

Flatten a parent-child hierarchy for Power BI:

// PATH function creates a pipe-delimited path
EmployeePath = PATH(Employee[EmployeeID], Employee[ManagerID])

// PATHLENGTH for depth
Depth = PATHLENGTH(Employee[EmployeePath])

// Extract each level
Level1 = LOOKUPVALUE(
    Employee[EmployeeName],
    Employee[EmployeeID],
    VALUE(PATHITEM(Employee[EmployeePath], 1))
)
Level2 = LOOKUPVALUE(
    Employee[EmployeeName],
    Employee[EmployeeID],
    VALUE(PATHITEM(Employee[EmployeePath], 2))
)

// Rollup measure across hierarchy
Team Sales =
VAR CurrentPath = SELECTEDVALUE(Employee[EmployeePath])
RETURN
CALCULATE(
    [Total Sales],
    FILTER(
        ALL(Employee),
        PATHCONTAINS(Employee[EmployeePath],
            SELECTEDVALUE(Employee[EmployeeID]))
    )
)

4. Basket Analysis (Products Bought Together)

// Customers who bought Product A
CustomersWithA =
CALCULATE(
    DISTINCTCOUNT(Sales[CustomerID]),
    FILTER(ALL(Sales), Sales[ProductID] = SELECTEDVALUE(Products[ProductID]))
)

// Customers who bought BOTH Product A and current product
CustomersWith Both =
VAR SelectedProduct = SELECTEDVALUE(Products[ProductID])
RETURN
CALCULATE(
    DISTINCTCOUNT(Sales[CustomerID]),
    FILTER(
        ALL(Sales),
        Sales[CustomerID] IN
            SELECTCOLUMNS(
                FILTER(ALL(Sales), Sales[ProductID] = SelectedProduct),
                "CID", Sales[CustomerID]
            )
    )
)

5. New vs Returning Customers

New Customers =
VAR CurrentDate = MAX(Date[Date])
VAR CurrentMonth = EOMONTH(CurrentDate, 0)
VAR MonthStart = EOMONTH(CurrentDate, -1) + 1
RETURN
CALCULATE(
    DISTINCTCOUNT(Sales[CustomerID]),
    FILTER(
        ALL(Sales),
        Sales[OrderDate] >= MonthStart && Sales[OrderDate] <= CurrentMonth
    ),
    FILTER(
        ALL(Sales),
        NOT(
            Sales[CustomerID] IN
            SELECTCOLUMNS(
                FILTER(ALL(Sales), Sales[OrderDate] < MonthStart),
                "CID", Sales[CustomerID]
            )
        )
    )
)

Returning Customers =
[Total Customers] - [New Customers]

6. ABC Classification (Pareto)

ABC Category =
VAR TotalSales = CALCULATE([Total Sales], ALL(Products))
VAR ProductSales =
    ADDCOLUMNS(
        ALL(Products[ProductID], Products[ProductName]),
        "ProdSales", [Total Sales]
    )
VAR RankedProducts =
    ADDCOLUMNS(
        ProductSales,
        "RunningTotal",
        SUMX(
            FILTER(ProductSales, [ProdSales] >= EARLIER([ProdSales])),
            [ProdSales]
        )
    )
VAR CurrentRunning =
    MAXX(
        FILTER(RankedProducts,
            [ProductID] = SELECTEDVALUE(Products[ProductID])),
        [RunningTotal]
    )
VAR Percentage = DIVIDE(CurrentRunning, TotalSales)
RETURN
    SWITCH(TRUE(),
        Percentage <= 0.7, "A",
        Percentage <= 0.9, "B",
        "C"
    )

7. Semi-Additive Measures (Snapshot/Balance)

For measures that should not sum across time (e.g., inventory balance, account balance):

// Last known balance
Current Balance =
CALCULATE(
    SUM(AccountBalance[Balance]),
    LASTDATE(Date[Date])
)

// Average daily balance
Average Daily Balance =
AVERAGEX(
    VALUES(Date[Date]),
    CALCULATE(SUM(AccountBalance[Balance]))
)

// Opening balance
Opening Balance =
CALCULATE(
    SUM(AccountBalance[Balance]),
    FIRSTDATE(Date[Date])
)

8. Dynamic Top N with "Others"

Sales with Others =
VAR TopN = 10
VAR RankVal =
    RANKX(
        ALL(Products[ProductName]),
        [Total Sales],
        ,
        DESC
    )
RETURN
    IF(
        RankVal <= TopN,
        [Total Sales],
        CALCULATE(
            [Total Sales],
            FILTER(
                ALL(Products[ProductName]),
                RANKX(ALL(Products[ProductName]), [Total Sales], , DESC) > TopN
            )
        )
    )

9. Currency Conversion

Sales in USD =
SUMX(
    Sales,
    VAR SaleDate = Sales[OrderDate]
    VAR SourceCurrency = RELATED(Region[CurrencyCode])
    VAR ExchangeRate =
        CALCULATE(
            SELECTEDVALUE(ExchangeRates[Rate]),
            ExchangeRates[Date] = SaleDate,
            ExchangeRates[FromCurrency] = SourceCurrency,
            ExchangeRates[ToCurrency] = "USD"
        )
    RETURN Sales[Amount] * ExchangeRate
)

10. Disconnected Slicer Tables

Create slicers that control measure behavior without filtering data:

// Disconnected table for metric selection
MetricSelector = DATATABLE(
    "Metric", STRING,
    {{"Revenue"}, {"Profit"}, {"Units"}, {"Margin %"}}
)

// Dynamic measure
Selected Metric =
SWITCH(
    SELECTEDVALUE(MetricSelector[Metric]),
    "Revenue", [Total Revenue],
    "Profit", [Total Profit],
    "Units", [Total Units],
    "Margin %", [Profit Margin %],
    BLANK()
)

11. Last Non-Blank Value

Last Reported Value =
CALCULATE(
    SELECTEDVALUE(Metrics[Value]),
    LASTNONBLANK(
        Date[Date],
        CALCULATE(COUNTROWS(Metrics))
    )
)

12. Cumulative Total

Cumulative Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL(Date[Date]),
        Date[Date] <= MAX(Date[Date])
    )
)

// More performant version:
Cumulative Sales v2 =
VAR LastDate = MAX(Date[Date])
RETURN
    CALCULATE(
        [Total Sales],
        Date[Date] <= LastDate,
        REMOVEFILTERS(Date[Date])
    )

13. Percentage of Parent (Visual Totals)

// % of parent category
% of Category =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALLSELECTED(Products[SubCategory]))
)

// % of grand total
% of Grand Total =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALL(Products))
)

// % of column total (respecting slicer)
% of Column =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALLSELECTED())
)

14. Handling Many-to-Many Relationships

// Using bridge table with TREATAS
Sales for Student =
VAR StudentCourses =
    CALCULATETABLE(
        VALUES(StudentCourse[CourseID]),
        TREATAS(VALUES(Students[StudentID]), StudentCourse[StudentID])
    )
RETURN
    CALCULATE(
        [Total Sales],
        TREATAS(StudentCourses, CourseSales[CourseID])
    )

15. SWITCH with Multiple Conditions

Customer Tier =
VAR TotalSpend = [Customer Lifetime Value]
VAR OrderCount = [Total Orders]
RETURN
SWITCH(
    TRUE(),
    TotalSpend > 10000 && OrderCount > 50, "Platinum",
    TotalSpend > 5000 && OrderCount > 20, "Gold",
    TotalSpend > 1000 && OrderCount > 5, "Silver",
    "Bronze"
)

16. Window Functions - Running Total

Running Total =
CALCULATE(
    [Total Sales],
    WINDOW(1, ABS, 0, REL,
        ALLSELECTED(Date[YearMonth]),
        ORDERBY(Date[YearMonth], ASC)
    )
)

17. Window Functions - Moving Average

3M Moving Average =
VAR WindowSize = 3
RETURN
AVERAGEX(
    WINDOW(-WindowSize + 1, REL, 0, REL,
        ALLSELECTED(Date[YearMonth]),
        ORDERBY(Date[YearMonth], ASC)
    ),
    [Total Sales]
)

18. Window Functions - Rank with PARTITIONBY

Product Rank in Category =
RANKX(
    ALLSELECTED(Products[ProductName]),
    [Total Sales],
    ,
    DESC
)

// Using RANK function (cleaner for window operations):
Product Rank v2 =
RANK(
    DENSE,
    ALLSELECTED(Products[ProductName]),
    ORDERBY([Total Sales], DESC),
    PARTITIONBY(Products[Category])
)

19. User-Defined Functions (UDF) Pattern (September 2025 Preview)

// Define once, reuse across measures
DEFINE
FUNCTION SafeGrowth = (current : NUMERIC, previous : NUMERIC) =>
    IF(previous = 0 || ISBLANK(previous),
        BLANK(),
        DIVIDE(current - previous, ABS(previous))
    )

// Use in multiple measures:
// YoY Growth = SafeGrowth([Total Sales], [PY Sales])
// MoM Growth = SafeGrowth([Total Sales], [PM Sales])

Parameter modes:

• val (eager): Evaluated before function executes -- use for simple scalar inputs
• expr (lazy): Evaluated in the function's context -- use for context-sensitive calculations

20. Dynamic Format Strings with Calculation Groups

// Calculation group: Currency Converter
// Calculation item: "Convert to Target"
// Expression:
VAR TargetCurrency = SELECTEDVALUE(CurrencySelector[Currency], "USD")
VAR Rate = LOOKUPVALUE(ExchangeRates[Rate],
    ExchangeRates[ToCurrency], TargetCurrency,
    ExchangeRates[Date], MAX(Date[Date]))
RETURN
    SELECTEDMEASURE() * Rate

// Format string expression:
VAR TargetCurrency = SELECTEDVALUE(CurrencySelector[Currency], "USD")
RETURN
    SWITCH(TargetCurrency,
        "USD", "$#,##0.00",
        "EUR", "€#,##0.00",
        "GBP", "£#,##0.00",
        "JPY", "¥#,##0",
        "#,##0.00"
    )

21. INFO Functions for Self-Documenting Models

// Create a calculated table that lists all measures
Model Documentation =
ADDCOLUMNS(
    SELECTCOLUMNS(
        INFO.VIEW.MEASURES(),
        "Measure", [Name],
        "Description", [Description],
        "DAX", [Expression],
        "Format", [FormatString]
    ),
    "Table", RELATED(INFO.VIEW.TABLES()[Name]),
    "Updated", NOW()
)

22. Calendar-Based Time Intelligence (September 2025 Preview)

// Week-to-date total using custom calendar
WTD Sales = TOTALWTD([Total Sales], Date[Date])

// Previous week comparison
PW Sales = CALCULATE([Total Sales], PREVIOUSWEEK(Date[Date]))

// Week-over-Week growth
WoW Growth % =
VAR Current = [Total Sales]
VAR PW = CALCULATE([Total Sales], PREVIOUSWEEK(Date[Date]))
RETURN DIVIDE(Current - PW, PW)

Requires a calendar defined in the model via Enhanced Time Intelligence preview feature.