Power BI Cheat Sheet
Last updated
Measures, columns and tables
A measure is computed for each cell of a visual; a calculated column is computed once per row and stored.
| What it does | DAX |
|---|---|
| Create a measure | Total Sales = SUM ( Sales[Amount] ) |
| Create a calculated column (one value per row) | Line Total = Sales[Quantity] * Sales[Unit Price] |
| Create a calculated table | Top Customers = TOPN ( 10, Customer, [Total Sales] ) |
| Refer to a column: table name, then the column in brackets | Sales[Amount] |
| Quote a table name that has a space or is a reserved word | 'Date'[Date] |
| Refer to a measure: brackets only | [Total Sales] |
| Use a measure inside another measure | Average Price = DIVIDE ( [Total Sales], [Total Units] ) |
Aggregation functions
Each one summarizes a column in the current filter context.
| What it does | DAX |
|---|---|
| Add up a column | SUM ( Sales[Amount] ) |
| Average of a column | AVERAGE ( Sales[Amount] ) |
| Smallest value | MIN ( Sales[Order Date] ) |
| Largest value | MAX ( Sales[Order Date] ) |
| Count the rows of a table | COUNTROWS ( Sales ) |
| Count the non blank values of a column | COUNT ( Sales[Amount] ) |
| Count distinct values | DISTINCTCOUNT ( Sales[Customer Key] ) |
| Divide safely: blank instead of an error when the bottom is 0 | DIVIDE ( [Total Sales], [Total Units] ) |
| Divide with a value to show when the bottom is 0 | DIVIDE ( [Profit], [Total Sales], 0 ) |
Iterators (the X functions)
They go through a table row by row, evaluate an expression for each row, then aggregate the results.
| What it does | DAX |
|---|---|
| Multiply per row, then add up | SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] ) |
| Average of a per row result | AVERAGEX ( Sales, Sales[Quantity] * Sales[Unit Price] ) |
| Largest per row result | MAXX ( Sales, Sales[Quantity] * Sales[Unit Price] ) |
| Average of a measure per customer | AVERAGEX ( VALUES ( Customer[Customer Key] ), [Total Sales] ) |
| Use a column of a related table in the expression | SUMX ( Sales, Sales[Quantity] * RELATED ( Product[Unit Cost] ) ) |
| Count the rows that pass a test | COUNTROWS ( FILTER ( Sales, Sales[Quantity] >= 3 ) ) |
| Rank every product by a measure | RANKX ( ALL ( Product[Product] ), [Total Sales] ) |
| Rank with no gaps after ties (1, 1, 2) | RANKX ( ALL ( Product[Product] ), [Total Sales], , DESC, DENSE ) |
| Join values into one text | CONCATENATEX ( VALUES ( Product[Category] ), Product[Category], ", " ) |
CALCULATE and filter functions
CALCULATE evaluates an expression with changed filters. Filters on the same column replace the old one; filters on different columns combine with AND.
| What it does | DAX |
|---|---|
| Filter on one value | CALCULATE ( [Total Sales], Product[Category] = "Bikes" ) |
| Filter on a list of values | CALCULATE ( [Total Sales], Product[Color] IN { "Red", "Blue" } ) |
| Two filters (AND) | CALCULATE ( [Total Sales], Product[Color] = "Red", Store[Country] = "Spain" ) |
| OR across two columns | CALCULATE ( [Total Sales], FILTER ( ALL ( Product[Color], Product[Brand] ), Product[Color] = "Red" || Product[Brand] = "Contoso" ) ) |
| Filter by a measure | CALCULATE ( [Total Sales], FILTER ( VALUES ( Customer[Customer Key] ), [Total Sales] > 1000 ) ) |
| Keep the existing filter and intersect with it | CALCULATE ( [Total Sales], KEEPFILTERS ( Product[Color] IN { "Red", "Blue" } ) ) |
| Remove the filters on a column | CALCULATE ( [Total Sales], REMOVEFILTERS ( Product[Category] ) ) |
| Remove every filter on a table | CALCULATE ( [Total Sales], ALL ( Product ) ) |
| Remove all filters on a table except some columns | CALCULATE ( [Total Sales], ALLEXCEPT ( Store, Store[Country] ) ) |
| Keep the slicer selection, drop the visual's own rows | CALCULATE ( [Total Sales], ALLSELECTED ( Product[Category] ) ) |
| Use an inactive relationship | CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[Ship Date], 'Date'[Date] ) ) |
| Let a filter flow both ways for one measure | CALCULATE ( COUNTROWS ( Customer ), CROSSFILTER ( Sales[Customer Key], Customer[Customer Key], BOTH ) ) |
| Filter by a table with no relationship | CALCULATE ( [Total Sales], TREATAS ( VALUES ( Budget[Category] ), Product[Category] ) ) |
Time intelligence
These need a date table: one row per day, no gaps, marked as a date table.
| What it does | DAX |
|---|---|
| Year to date | TOTALYTD ( [Total Sales], 'Date'[Date] ) |
| Year to date, fiscal year ending 30 June | TOTALYTD ( [Total Sales], 'Date'[Date], "6/30" ) |
| Year to date inside CALCULATE | CALCULATE ( [Total Sales], DATESYTD ( 'Date'[Date] ) ) |
| Quarter to date | TOTALQTD ( [Total Sales], 'Date'[Date] ) |
| Month to date | TOTALMTD ( [Total Sales], 'Date'[Date] ) |
| Same period last year | CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) |
| Shift the dates by an interval (here one month back) | CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) ) |
| The whole previous month | CALCULATE ( [Total Sales], PREVIOUSMONTH ( 'Date'[Date] ) ) |
| The whole period one year back | CALCULATE ( [Total Sales], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) ) |
| The last 12 months up to the last visible date | CALCULATE ( [Total Sales], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ) ) |
| A fixed date range | CALCULATE ( [Total Sales], DATESBETWEEN ( 'Date'[Date], DATE ( 2025, 1, 1 ), DATE ( 2025, 3, 31 ) ) ) |
Common measure patterns
Copy, then rename the tables and columns to match your model.
| What it does | DAX |
|---|---|
| Percent of all categories | Category % = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( Product[Category] ) ) ) |
| Percent of what the slicers leave | Visible % = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( Product[Category] ) ) ) |
| Year over year growth | YoY % = VAR LastYear = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) RETURN DIVIDE ( [Total Sales] - LastYear, LastYear ) |
| Running total over all time | Running Total = CALCULATE ( [Total Sales], FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) |
| 7 day moving average (per day with sales) | Sales 7 Day Avg = AVERAGEX ( DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -7, DAY ), [Total Sales] ) |
| Show a value only for the top 5 | Top 5 Sales = IF ( RANKX ( ALLSELECTED ( Product[Product] ), [Total Sales] ) <= 5, [Total Sales] ) |
| Count customers above a threshold | Big Customers = COUNTROWS ( FILTER ( VALUES ( Customer[Customer Key] ), [Total Sales] > 1000 ) ) |
| Make a total add up its rows | Bonus Total = SUMX ( VALUES ( Product[Category] ), [Bonus] ) |
| Show a value only at one level of a matrix | Category Rank = IF ( ISINSCOPE ( Product[Category] ), RANKX ( ALL ( Product[Category] ), [Total Sales] ) ) |
| Closing balance: the last date that has a value | Closing Units = CALCULATE ( SUM ( Inventory[Units] ), LASTNONBLANK ( 'Date'[Date], CALCULATE ( SUM ( Inventory[Units] ) ) ) ) |
Logical and information functions
For conditions, and for asking what the filter context holds.
| What it does | DAX |
|---|---|
| One condition | IF ( [Total Sales] > 1000, "High", "Low" ) |
| Several values of one expression | SWITCH ( Store[Country], "Spain", "ES", "Portugal", "PT", "Other" ) |
| Ranges, checked top to bottom | SWITCH ( TRUE (), [Total Sales] >= 10000, "A", [Total Sales] >= 1000, "B", "C" ) |
| AND and OR | IF ( Sales[Quantity] > 2 && Sales[Amount] > 50, "Big", "Small" ) |
| Is the value blank? | ISBLANK ( [Total Sales] ) |
| First value that is not blank | COALESCE ( [Total Sales], 0 ) |
| Does the filter leave exactly one value? | HASONEVALUE ( Product[Category] ) |
| That one value, or a default | SELECTEDVALUE ( Product[Category], "All categories" ) |
| Is this row of the visual grouped by the column? | ISINSCOPE ( Product[Category] ) |
| Return a blank | BLANK () |
Text functions
The & operator joins text; FORMAT turns a number or date into text.
| What it does | DAX |
|---|---|
| Join text | Customer[First Name] & " " & Customer[Last Name] |
| Format a number | FORMAT ( [Total Sales], "#,0.00" ) |
| Format a date | FORMAT ( 'Date'[Date], "mmm yyyy" ) |
| First characters | LEFT ( Product[Code], 3 ) |
| Characters from a position | MID ( Product[Code], 4, 2 ) |
| Length | LEN ( Product[Name] ) |
| Upper case | UPPER ( Store[City] ) |
| Remove extra spaces | TRIM ( Customer[Name] ) |
| Replace text | SUBSTITUTE ( Customer[Phone], " ", "" ) |
| Position of a text, ignoring case (0 when missing) | SEARCH ( "pro", Product[Name], 1, 0 ) |
Date functions
Dates are numbers underneath, so you can subtract them to get days.
| What it does | DAX |
|---|---|
| Today's date | TODAY () |
| Build a date | DATE ( 2025, 12, 31 ) |
| Year of a date | YEAR ( 'Date'[Date] ) |
| Month number | MONTH ( 'Date'[Date] ) |
| Weekday, Monday as 1 | WEEKDAY ( 'Date'[Date], 2 ) |
| Week number, weeks starting Monday | WEEKNUM ( 'Date'[Date], 2 ) |
| Last day of the month | EOMONTH ( 'Date'[Date], 0 ) |
| Same day one month earlier | EDATE ( 'Date'[Date], -1 ) |
| Days between two dates | DATEDIFF ( Sales[Order Date], Sales[Ship Date], DAY ) |
| A date table from fixed dates | CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) ) |
| A date table from the dates in the model | CALENDARAUTO () |
Relationships and lookups
RELATED follows a relationship from the many side to the one side.
| What it does | DAX |
|---|---|
| Value from the one side (in a column on Sales) | RELATED ( Product[Category] ) |
| The many side rows of the current row (in a column on Customer) | COUNTROWS ( RELATEDTABLE ( Sales ) ) |
| Look up a value with no relationship | LOOKUPVALUE ( Product[Price], Product[SKU], Sales[SKU] ) |
| Parent child path (for hierarchies and row-level security) | PATH ( Employee[Employee ID], Employee[Manager ID] ) |
| The signed in user, for dynamic row-level security | USERPRINCIPALNAME () |
Table functions
They return tables: use them in a calculated table, inside CALCULATE, or in an iterator.
| What it does | DAX |
|---|---|
| Distinct values in the current filter | VALUES ( Product[Category] ) |
| Rows that pass a test | FILTER ( Product, Product[Price] > 100 ) |
| The top N rows by a measure | TOPN ( 5, ALL ( Product[Product] ), [Total Sales] ) |
| Group by columns and add measures | SUMMARIZECOLUMNS ( Product[Category], "Sales", [Total Sales] ) |
| Add a column to a table | ADDCOLUMNS ( VALUES ( Product[Category] ), "Sales", [Total Sales] ) |
| Pick and rename columns | SELECTCOLUMNS ( Customer, "Name", Customer[Name], "City", Customer[City] ) |
| A list of numbers | GENERATESERIES ( 1, 10, 1 ) |
| Every combination of two tables | CROSSJOIN ( VALUES ( 'Date'[Year] ), VALUES ( Product[Category] ) ) |
| Stack two tables with the same columns | UNION ( 'Sales 2024', 'Sales 2025' ) |
Variables
VAR names a step and computes it once; RETURN gives the result.
| What it does | DAX |
|---|---|
| One variable | Margin % = VAR Revenue = [Total Sales] RETURN DIVIDE ( Revenue - [Total Cost], Revenue ) |
| Several variables | Sales vs LY = VAR CY = [Total Sales] VAR PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) RETURN CY - PY |
| A table in a variable | Top 5 Total = VAR Top5 = TOPN ( 5, ALL ( Product[Product] ), [Total Sales] ) RETURN CALCULATE ( [Total Sales], Top5 ) |
Power Query (M) steps
Power Query shapes data before it loads. These are the M formulas behind the common buttons.
| What it does | M |
|---|---|
| Keep the rows that match | Table.SelectRows(Source, each [Status] = "Paid") |
| Remove columns | Table.RemoveColumns(Source, {"Notes", "Temp"}) |
| Set column types | Table.TransformColumnTypes(Source, {{"Amount", type number}, {"Date", type date}}) |
| Rename a column | Table.RenameColumns(Source, {{"amt", "Amount"}}) |
| Add a column | Table.AddColumn(Source, "Total", each [Quantity] * [Price], type number) |
| Merge (join) two queries | Table.NestedJoin(Sales, {"ProductID"}, Products, {"ProductID"}, "Product", JoinKind.LeftOuter) |
| Append (stack) queries | Table.Combine({Sales2024, Sales2025}) |
| Unpivot columns into rows | Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Amount") |
| Group and aggregate | Table.Group(Source, {"Category"}, {{"Sales", each List.Sum([Amount]), type number}}) |
| Replace errors with a value | Table.ReplaceErrorValues(Source, {{"Amount", 0}}) |
This Power BI cheat sheet lists the DAX you write most often, on one page: aggregations, the X iterators, CALCULATE and the functions that change filters, time intelligence, text and date functions, and measure patterns you can copy, such as percent of total, year over year growth and running totals.
The examples use a sales model with Sales, Product, Customer and a 'Date' table; rename them to match yours. The DAX was parsed by the engine behind Coddy's Power BI course, where you can write it in a Power BI workspace in your browser and have it checked.
Power BI cheat sheet FAQ
Is this Power BI cheat sheet free?
Yes. Read it here or bookmark it; there is no sign up.
What is the difference between a measure and a calculated column?
A calculated column is computed once per row at refresh and stored, so you can slice by it. A measure is computed for each cell of a visual, in that cell's filters, and stores nothing. Totals and ratios belong in measures.
What does CALCULATE do?
It evaluates an expression with changed filters: it adds, replaces or removes filters, then computes the result. Most DAX beyond simple totals is CALCULATE with the right filter arguments.
Why use DIVIDE instead of the / operator?
DIVIDE returns a blank (or a value you choose) when the bottom is zero or blank. The / operator returns infinity or an error value instead, which shows up in visuals.Do I need a date table for time intelligence?
Yes. Functions such as
TOTALYTD and SAMEPERIODLASTYEAR need a table with one row per day, no gaps and whole years, related to your facts and marked as a date table.Does this DAX work in Excel Power Pivot and Analysis Services?
The DAX language is shared, so almost all of it works there too. A few newer functions arrive in Power BI first.
Where can I practise DAX?
In Coddy's Power BI course: each lesson gives you a Power BI workspace in the browser and checks your measure against the lesson's data and hidden copies of it.
Related resources
More cheat sheets
