DAX (Data Analysis Expressions) Cheat Sheet

Updated 2026-05-26

Next Topic: Decision Intelligence Cheat Sheet

🧠Study flashcards on this topic139 cards · spaced repetition→

DAX (Data Analysis Expressions) is a functional formula language developed by Microsoft for data modeling and analytical calculations in Power BI, Excel Power Pivot, and SQL Server Analysis Services (and Azure Analysis Services / Microsoft Fabric). Unlike traditional spreadsheet formulas, DAX operates on tables and columns rather than cell references, enabling dynamic aggregations that automatically respond to filter context. The key to DAX mastery lies in understanding evaluation contexts: filter context (what's being filtered), row context (iterating through rows), and context transition (how CALCULATE bridges the two) — once you grasp these three concepts, even complex DAX patterns become intuitive. In 2025–2026, DAX gained two landmark features: user-defined functions (the biggest language change since variables) and calendar-based time intelligence for non-Gregorian calendars.

What This Cheat Sheet Covers

This topic spans 29 focused tables and 257 indexed concepts, 139 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Core Calculation TypesTable 2: Evaluation ContextsTable 3: Essential Aggregation FunctionsTable 4: Iterator (X) FunctionsTable 5: CALCULATE — The Most Important FunctionTable 6: Filter Modification FunctionsTable 7: Time Intelligence FunctionsTable 8: Calendar-Based Time IntelligenceTable 9: Date and Time FunctionsTable 10: Logical FunctionsTable 11: Text FunctionsTable 12: Table Manipulation FunctionsTable 13: Relationship FunctionsTable 14: Information FunctionsTable 15: Lookup and Search FunctionsTable 16: Statistical FunctionsTable 17: Math and Trigonometry FunctionsTable 18: Financial FunctionsTable 19: Conversion FunctionsTable 20: Parent-Child Hierarchy FunctionsTable 21: Window FunctionsTable 22: Visual Calculation FunctionsTable 23: User-Defined FunctionsTable 24: Advanced Variables and OptimizationTable 25: Common DAX PatternsTable 26: Performance Optimization TechniquesTable 27: DAX Query SyntaxTable 28: DAX OperatorsTable 29: Best Practices and Naming Conventions

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Core Calculation Types

The four building blocks of DAX define where calculations live and when they are evaluated. Choosing the wrong type is the most common beginner mistake — especially confusing measures (dynamic, context-aware) with calculated columns (static, row-level, stored in the model).

Type	Example	Description
Measure	`Total Sales = SUM(Sales[Amount])`	• Dynamic calculation evaluated in filter context • recalculates on every slicer/filter/visual selection • does not add to model size — the preferred default.
Calculated Column	`Profit = Sales[Revenue] - Sales[Cost]`	• Static row-level calculation evaluated in row context • computed once during refresh, stored in model • increases file size — prefer measures or Power Query when possible.
Calculated Table	`DateTable = CALENDAR(DATE(2020,1,1), DATE(2025,12,31))`	• New table created from a DAX expression • useful for date tables, role-playing dimensions, and data modeling • refreshed with the dataset.
Visual Calculation	`RunningTotal = RUNNINGSUM([Total Sales])`	• Calculation scoped to the visual level — Generally Available in Power BI May 2026 • uses visual axes and partitions • simplifies running totals, rankings, and percent-of-parent without complex DAX.

Table 2: Evaluation Contexts

Evaluation context is the mechanism DAX uses to determine which rows are visible during a calculation. Filter and row context interact in non-obvious ways — context transition (triggered by CALCULATE) is the most advanced concept and the source of most DAX bugs.

Context	Example	Description
Filter Context	`SUM(Sales[Amount])` with slicer on Year	• Set of filters applied to the data model before evaluation • created by slicers, page filters, rows/columns in visuals, and relationships

The rest of this section is part of the full cheat sheet

Table 3: Essential Aggregation Functions

These are the functions most frequently used in measures and calculated columns. Understanding the difference between COUNT/COUNTA/COUNTROWS and when blanks are included versus excluded prevents the most common aggregation bugs.

Function	Example	Description
SUM	`SUM(Sales[Amount])`	• Adds all numbers in a column • blank values treated as zero.

The rest of this section is part of the full cheat sheet

Table 4: Iterator (X) Functions

Iterator functions evaluate an expression for each row of a table in row context, then aggregate the results. They are essential when you need a calculation per row before aggregating — for example, line-item revenue before summing an order.

Function	Example	Description
SUMX	`SUMX(Sales, Sales[Qty] * Sales[Price])`	• Evaluates expression for each row, then sums • essential for row-level calculations before aggregation

The rest of this section is part of the full cheat sheet

Table 1: Core Calculation Types

Type	Example	Description
Measure	`Total Sales = SUM(Sales[Amount])`	• Dynamic calculation evaluated in filter context • recalculates on every slicer/filter/visual selection • does not add to model size — the preferred default.
Calculated Column	`Profit = Sales[Revenue] - Sales[Cost]`	• Static row-level calculation evaluated in row context • computed once during refresh, stored in model • increases file size — prefer measures or Power Query when possible.
Calculated Table	`DateTable = CALENDAR(DATE(2020,1,1), DATE(2025,12,31))`	• New table created from a DAX expression • useful for date tables, role-playing dimensions, and data modeling • refreshed with the dataset.
Visual Calculation	`RunningTotal = RUNNINGSUM([Total Sales])`	• Calculation scoped to the visual level — Generally Available in Power BI May 2026 • uses visual axes and partitions • simplifies running totals, rankings, and percent-of-parent without complex DAX.

Table 2: Evaluation Contexts

Context	Example	Description
Filter Context	`SUM(Sales[Amount])` with slicer on Year	• Set of filters applied to the data model before evaluation • created by slicers, page filters, rows/columns in visuals, and relationships

Table 3: Essential Aggregation Functions

Function	Example	Description
SUM	`SUM(Sales[Amount])`	• Adds all numbers in a column • blank values treated as zero.

Table 4: Iterator (X) Functions

Function	Example	Description
SUMX	`SUMX(Sales, Sales[Qty] * Sales[Price])`	• Evaluates expression for each row, then sums • essential for row-level calculations before aggregation