All lessons
DAX cheat sheet
The DAX patterns of the course on one page, written for the Northbay model (ShipDate and the Users table are extra examples), each with what it does in one line and the lesson that explains it. Try any of them in the DAX playground.
Model and columns
Date table Lesson 3.3
Date = ADDCOLUMNS(CALENDAR(DATE(2024,1,1), DATE(2025,12,31)),
"Year", YEAR([Date]), "MonthNo", MONTH([Date]),
"Month", FORMAT([Date], "mmm")) One row per day, whole years. Then Mark as date table.
Calculated column Lesson 3.4
Sales[Margin] = Sales[Amount] - Sales[Quantity] * Sales[UnitCost] Worked out once per row at refresh and stored.
Value from the one side Lesson 4.4
Sales[Category] = RELATED(Product[Category]) Fetches the related product's category for each sales line.
Rows from the many side Lesson 4.4
Product[Lines] = COUNTROWS(RELATEDTABLE(Sales)) How many sales lines each product has.
First measures
Ratio Lesson 4.2
Margin % = DIVIDE([Total Margin], [Total Sales]) Blank instead of an error when the bottom is zero.
Row by row Lesson 4.3
Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) Calculates per row, then adds up.
Count distinct Lesson 4.1
Products Sold = DISTINCTCOUNT(Sales[ProductKey]) How many different products appear.
Variables and bands Lesson 4.5
Band =
VAR s = [Total Sales]
RETURN SWITCH(TRUE(), s >= 100000, "High", s >= 50000, "Mid", "Low") Name a step once; SWITCH(TRUE()) for ranges.
CALCULATE and filter context
Fixed filter Lesson 5.2
Home Sales = CALCULATE([Total Sales], Product[Category] = "Home") Replaces any filter on Category, keeps the rest.
Add, don't replace Lesson 5.2
CALCULATE([Total Sales], KEEPFILTERS(Product[Category] = "Home")) Intersects with the existing Category filter.
Percent of total Lesson 5.3
Share = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Store))) Every row divided by the chain total.
Percent of selection Lesson 5.3
DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Store))) Respects slicers; ignores the visual's own rows.
Condition on rows Lesson 5.4
Big Lines = CALCULATE([Total Sales], FILTER(Sales, Sales[Amount] > 100)) FILTER for row-by-row or measure conditions.
Context transition Lesson 5.5
Best Day = MAXX(VALUES('Date'[Date]), [Total Sales]) A measure inside an iterator sees each row as a filter.
Rank Lesson 5.6
Rank = RANKX(ALL(Product[Product]), [Total Sales]) Compares each product with every product.
Inactive relationship Lesson 3.2
CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])) Uses the dashed relationship for this measure only.
Time intelligence
Year to date Lesson 6.1
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date]) From 1 January to the last date in context.
Last year Lesson 6.2
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) The same dates one year earlier.
Growth Lesson 6.2
Growth % = DIVIDE([Total Sales] - [Sales LY], [Sales LY]) Change on last year as a share of last year.
Rolling 28 days Lesson 6.3
Sales R28 = CALCULATE([Total Sales],
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -28, DAY)) The 28 days ending on the last date in context.
Targets at month grain Lesson 6.4
Target = CALCULATE(SUM(Targets[Target]),
TREATAS(VALUES('Date'[YearMonth]), Targets[YearMonth])) Moves the month filter onto a table with no date relationship.
Queries and security
Test a measure Lesson 5.7
EVALUATE
SUMMARIZECOLUMNS(Store[Store], "Sales", [Total Sales])
ORDER BY [Sales] DESC Returns a table: run it in DAX query view.
Dynamic RLS Lesson 8.2
Users[Email] = USERPRINCIPALNAME() One role; each person sees their own rows.