Home → Data Science & ML → DAX Formulas - Complete Reference Guide

DAX Formulas - Complete Reference Guide Cheat Sheet

Last Updated: November 21, 2025

Calculated Columns vs Measures

Aspect Calculated Column Measure
When computed At data refresh time At query time
Storage Stored in model (uses memory) Not stored (calculated on-demand)
Row context Yes - evaluates row by row No - requires CALCULATE/iterators
Filter context No automatic filter context Yes - responds to slicers/filters
Use case Static values, categorization, flags Aggregations, KPIs, dynamic calculations
Syntax ColumnName = [Field1] + [Field2] MeasureName = SUM(Table[Field])
Best for Data transformation, grouping Reporting, dashboards, visuals

Basic Aggregation Functions

Function Example
SUM Total Sales = SUM(Sales[Amount])
AVERAGE Avg Price = AVERAGE(Products[Price])
MIN Lowest Price = MIN(Products[Price])
MAX Highest Price = MAX(Products[Price])
COUNT Row Count = COUNT(Sales[OrderID])
COUNTA Non-Blank Count = COUNTA(Sales[OrderID])
COUNTROWS Total Rows = COUNTROWS(Sales)
DISTINCTCOUNT Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Statistical Functions

Function Example
MEDIAN Median Sales = MEDIAN(Sales[Amount])
STDEV.P Std Dev = STDEV.P(Sales[Amount])
VAR.P Variance = VAR.P(Sales[Amount])
RANKX Product Rank = RANKX(ALL(Products), [Total Sales])
PERCENTILE.INC 90th Percentile = PERCENTILE.INC(Sales[Amount], 0.9)

Filter Functions

Function Purpose
CALCULATE Sales 2023 = CALCULATE(SUM(Sales[Amount]), Year[Year] = 2023)
FILTER High Value Sales = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Amount] > 1000))
ALL Total All Sales = CALCULATE(SUM(Sales[Amount]), ALL(Sales))
ALLEXCEPT Total by Product = CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Product]))
ALLSELECTED % of Visible = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED()))
REMOVEFILTERS No Filters = CALCULATE([Total Sales], REMOVEFILTERS())
KEEPFILTERS Keep Context = CALCULATE([Total Sales], KEEPFILTERS(Products[Category] = "Electronics"))

Logical Functions

Function Example
IF Status = IF(Sales[Amount] > 1000, "High", "Low")
SWITCH Category = SWITCH(Products[Type], "A", "Premium", "B", "Standard", "Other")
AND Valid = IF(AND(Sales[Qty] > 0, Sales[Amount] > 0), "Yes", "No")
OR Flag = IF(OR(Sales[Qty] > 100, Sales[Amount] > 5000), "Yes", "No")
NOT Inactive = IF(NOT(Customers[Active]), "Inactive", "Active")
IFERROR Safe Calc = IFERROR([Calc], 0)
ISBLANK Has Value = IF(ISBLANK(Sales[Notes]), "No", "Yes")

Time Intelligence Functions

Function Example
TOTALYTD YTD Sales = TOTALYTD(SUM(Sales[Amount]), Dates[Date])
TOTALQTD QTD Sales = TOTALQTD(SUM(Sales[Amount]), Dates[Date])
TOTALMTD MTD Sales = TOTALMTD(SUM(Sales[Amount]), Dates[Date])
SAMEPERIODLASTYEAR Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
PARALLELPERIOD Prev Month = CALCULATE([Total Sales], PARALLELPERIOD(Dates[Date], -1, MONTH))
DATEADD Last Month = CALCULATE([Total Sales], DATEADD(Dates[Date], -1, MONTH))
DATESYTD YTD Custom = CALCULATE([Total Sales], DATESYTD(Dates[Date]))
DATESBETWEEN Period Sales = CALCULATE([Total Sales], DATESBETWEEN(Dates[Date], DATE(2023,1,1), DATE(2023,12,31)))

Time Intelligence - Year-over-Year

Metric DAX Formula
Prior year sales PY Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
YoY change YoY Change = [Total Sales] - [PY Sales]
YoY % change YoY % = DIVIDE([YoY Change], [PY Sales], 0)
YoY growth YoY Growth = IF([PY Sales] = 0, BLANK(), ([Total Sales] - [PY Sales]) / [PY Sales])

Iterator Functions (X Functions)

Function Purpose
SUMX Total Revenue = SUMX(Sales, Sales[Qty] * Sales[Price])
AVERAGEX Avg Order Value = AVERAGEX(Sales, Sales[Qty] * Sales[Price])
COUNTX Count High Sales = COUNTX(FILTER(Sales, Sales[Amount] > 1000), Sales[OrderID])
MAXX Max Order = MAXX(Sales, Sales[Qty] * Sales[Price])
MINX Min Order = MINX(Sales, Sales[Qty] * Sales[Price])
CONCATENATEX Product List = CONCATENATEX(Products, Products[Name], ", ")

Relationship Functions

Function Use Case
RELATED Category Name = RELATED(Categories[CategoryName])
RELATEDTABLE Total Orders = COUNTROWS(RELATEDTABLE(Orders))
USERELATIONSHIP Ship Date Sales = CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], Dates[Date]))
CROSSFILTER Bidirectional = CALCULATE([Measure], CROSSFILTER(Table1[ID], Table2[ID], BOTH))

Text Functions

Function Example
CONCATENATE Full Name = CONCATENATE(Customers[First], " " & Customers[Last])
LEFT First 3 = LEFT(Products[Code], 3)
RIGHT Last 2 = RIGHT(Products[Code], 2)
MID Middle Part = MID(Products[Code], 2, 3)
LEN Text Length = LEN(Products[Name])
UPPER Uppercase = UPPER(Customers[Name])
LOWER Lowercase = LOWER(Customers[Email])
TRIM Trimmed = TRIM(Customers[Name])
SUBSTITUTE Replaced = SUBSTITUTE(Products[Name], "Old", "New")
FORMAT Formatted = FORMAT([Total Sales], "$#,##0.00")

Date Functions

Function Example
YEAR Year = YEAR(Sales[OrderDate])
MONTH Month = MONTH(Sales[OrderDate])
DAY Day = DAY(Sales[OrderDate])
WEEKDAY Day of Week = WEEKDAY(Sales[OrderDate], 2)
WEEKNUM Week Number = WEEKNUM(Sales[OrderDate])
EOMONTH Month End = EOMONTH(Sales[OrderDate], 0)
DATE New Date = DATE(2023, 12, 31)
TODAY Current Date = TODAY()
NOW Current DateTime = NOW()

Common Patterns - Running Totals

Pattern DAX Formula
Running total Running Total = CALCULATE([Total Sales], FILTER(ALL(Dates[Date]), Dates[Date] <= MAX(Dates[Date])))
Moving average (3-month) 3M Avg = CALCULATE(AVERAGE(Sales[Amount]), DATESINPERIOD(Dates[Date], LASTDATE(Dates[Date]), -3, MONTH))
Cumulative total Cumulative = CALCULATE([Total Sales], FILTER(ALLSELECTED(Dates), Dates[Date] <= MAX(Dates[Date])))

Common Patterns - Ranking & Top N

Pattern DAX Formula
Rank by sales Rank = RANKX(ALL(Products[Product]), [Total Sales],, DESC, Dense)
Top 10 products Top 10 = IF([Rank] <= 10, [Total Sales], BLANK())
Top N with filter Top 5 Sales = CALCULATE([Total Sales], TOPN(5, ALL(Products), [Total Sales]))

Variables in DAX

Use Case Example
Store calculation Margin % =
VAR Revenue = SUM(Sales[Amount])
VAR Cost = SUM(Sales[Cost])
RETURN DIVIDE(Revenue - Cost, Revenue)
Improve performance Complex Calc =
VAR BaseValue = [Total Sales]
VAR GrowthRate = 0.15
RETURN BaseValue * (1 + GrowthRate)
Better readability YoY Growth =
VAR CurrentYear = [Total Sales]
VAR PriorYear = [PY Sales]
RETURN DIVIDE(CurrentYear - PriorYear, PriorYear)

Best Practices

Practice Why
Use measures, not calculated columns Measures are more flexible and don't increase model size
Use variables Improves performance and readability
DIVIDE instead of / Handles division by zero gracefully
Use ALL vs ALLEXCEPT carefully ALL removes all filters, ALLEXCEPT preserves some
Mark date table as date table Enables time intelligence functions
Use SELECTEDVALUE Safer than VALUES when expecting single value
Avoid calculated columns for aggregations Use measures instead for better performance
💡 Pro Tips:
  • Always create a separate date table with continuous dates for time intelligence
  • Use CALCULATE to modify filter context in measures
  • Variables (VAR) are evaluated once and stored, improving performance
  • Use ISFILTERED() to detect if a column is being filtered
  • BLANK() is different from 0 - use appropriately in calculations
  • Use DAX Studio (free tool) to test and optimize DAX queries
  • Format measures with FORMAT() for better visual display
  • Use SELECTEDVALUE() when you expect a single value from a column

Related Cheat Sheets

Browse all Data Science & ML →

← Back to Data Science & ML | Browse all categories | View all cheat sheets