DP-600 - DAX Functions and Optimization

Quick Navigation

DAX Basics
CALCULATE and Filter Context
Context Transition
Time Intelligence
Calculation Groups
Window Functions (OFFSET, INDEX, RANK)
DAX Optimization Tools
DAX Variables

DAX Basics

DAX (Data Analysis Expressions) is the formula language for Power BI, Analysis Services, and Fabric semantic models. It is used to write calculated columns, measures, and calculated tables.

DAX Object	Definition	Example
Measure	Dynamic aggregation evaluated at query time	`Total Sales = SUM(Sales[Amount])`
Calculated Column	Row-by-row computation stored in model	`Profit = Sales[Revenue] - Sales[Cost]`
Calculated Table	Table derived from other tables via DAX	`TopCustomers = TOPN(100, Customers, [Revenue])`

DAX uses table and column references: TableName[ColumnName]. Measures are referenced as [MeasureName] (no table prefix).

CALCULATE and Filter Context

CALCULATE is the most important DAX function. It evaluates an expression in a modified filter context:

// Sales for the East region only
East Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "East")

// Remove all filters on the Category column
All Category Sales = CALCULATE([Total Sales], ALL(Products[Category]))

// Sales for current year
CY Sales = CALCULATE([Total Sales], YEAR(Sales[Date]) = YEAR(TODAY()))

CALCULATE modifiers:

Function	Effect
`ALL(table/column)`	Removes all filters on the table/column
`ALLEXCEPT(table, col1, col2)`	Removes all filters EXCEPT listed columns
`ALLSELECTED(table)`	Removes filters from context but respects slicers/visual filters
`KEEPFILTERS`	Adds a filter without overriding existing ones
`REMOVEFILTERS`	Alias of ALL - removes all filters
`USERELATIONSHIP(col1, col2)`	Activates an inactive relationship for this calculation
`CROSSFILTER`	Changes the cross-filter direction for a relationship

Context Transition

Row context: the current row being evaluated (used in calculated columns and iterator functions). Filter context: the set of filters applied by visuals, slicers, and report filters (used in measures).

Context Transition: When CALCULATE is called inside a row context (e.g., inside an iterator), it automatically converts the row context into an equivalent filter context. This is why CALCULATE([Total Sales]) inside SUMX works correctly - the current row becomes a filter.

Iterator functions (X functions) iterate row-by-row and create row context:

// Weighted average unit price
Avg Unit Price = DIVIDE(SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]), SUM(Sales[Quantity]))

Other iterators: AVERAGEX, COUNTX, MINX, MAXX, RANKX, TOPN.

Time Intelligence Functions

Time intelligence functions require a properly marked date table with contiguous dates.

Function	Returns
`SAMEPERIODLASTYEAR`	Equivalent period in the previous year
`DATEADD(dates, n, interval)`	Shift dates by n intervals (DAY/MONTH/QUARTER/YEAR)
`TOTALYTD / TOTALQTD / TOTALMTD`	Year/Quarter/Month to date cumulative
`DATESYTD(dates, [year_end_date])`	Date filter from year start to current date
`PREVIOUSMONTH / PREVIOUSQUARTER / PREVIOUSYEAR`	Previous period

YoY Growth % = DIVIDE([Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date])),
                       CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date])))

Calculation Groups

Calculation groups are a model-level feature (created in Tabular Editor) that apply a set of calculation items to base measures. They solve the problem of replicating time intelligence logic across many measures.

// Calculation Group: "Time Intelligence"
// Calculation Item: "YTD"
CALCULATE(SELECTEDMEASURE(), DATESYTD(Date[Date]))

// Calculation Item: "PY"
CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(Date[Date]))

A single calculation group with "YTD", "PY", "MTD" items automatically applies to all measures when a user selects from the slicer - no need for YTD_Sales, YTD_Profit, YTD_Cost measures.

SELECTEDMEASURE() returns whatever measure is in the current filter context. SELECTEDMEASURENAME() returns its name as a string (useful for dynamic format strings).

Window Functions: OFFSET, INDEX, RANK, ROWNUMBER

Introduced in DAX 2022, these functions add SQL-style window analytics to DAX:

Function	Description
`RANK`	Rank of each row by a measure (with ties)
`ROWNUMBER`	Unique sequential row number (no ties)
`OFFSET(n, ...)`	Value from n rows before (-n) or after (+n) in a sorted partition
`INDEX(n, ...)`	Value at absolute position n in a sorted partition
`WINDOW(from, to, ...)`	Cumulative calculation over a defined row window

// Previous month Sales using OFFSET
Prev Month Sales = CALCULATE([Total Sales],
  OFFSET(-1, ALLSELECTED(Date[Month]), ORDERBY(Date[Month])))

DAX Optimization Tools

Tool	Purpose
DAX Studio	Free tool to write, profile, and debug DAX queries. Shows Server Timings (storage engine vs formula engine time). Run EVALUATE queries against a live model.
VertiPaq Analyzer	Analyzes in-memory column sizes, cardinality, and segment counts to find model bloat. Integrated in DAX Studio.
Tabular Editor 2 / 3	Edit model metadata - calculation groups, OLS, translations, perspectives - without opening Power BI Desktop.
ALM Toolkit	Compares and merges semantic model schemas - useful for deployment and merging model changes.

DAX Performance Tips: Prefer DIVIDE over /, avoid calculated columns for aggregations, use variables (VAR) to avoid repeated subexpression evaluation, prefer SUMX over nested CALCULATE where possible.

DAX Variables

Variables improve readability and performance by preventing re-evaluation of expressions:

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

Variables are evaluated once when the measure is called. Using VAR avoids repeating the same CALCULATE expression multiple times and avoids context issues in complex measures. Variables are immutable - they cannot be reassigned.

← Take DP-600 Practice Tests | Back to Study Topics

Search Tutorials