Power BI (DAX) cheat sheet

DAX is the formula language for measures and calculated columns in Power BI, Analysis Services, and Power Pivot. It is not the same as Power Query M, which is the separate language used to shape and load data before it reaches the model. Everything below is DAX. Most surprises in DAX come from filter context and context transition, so read the CALCULATE and time-intelligence notes carefully — that is usually where a measure quietly returns the wrong total.

Download PDF All PDF downloads →

Aggregations

The plain functions aggregate one column. The X variants iterate a table row by row, evaluating an expression per row before aggregating.

SUM

Total a column

SUM(Sales[Amount])

→ A number

SUMX

Sum a per-row expression

SUMX(Sales, Sales[Qty] * Sales[Price])

→ A number

Use the X version when the value you need does not exist as a single column.

AVERAGE

Mean of a column

AVERAGE(Sales[Amount])

→ A number

AVERAGEX

Mean of a per-row expression

AVERAGEX(Sales, Sales[Qty] * Sales[Price])

→ A number

MIN / MAX

Smallest or largest value

MAX(Sales[Amount])

→ A number

MINX / MAXX

Min/max of a per-row expression

MAXX(Sales, Sales[Qty] * Sales[Price])

→ A number

COUNT

Count numeric values

COUNT(Sales[OrderID])

→ A number

COUNTA

Count non-blank values

COUNTA(Sales[Notes])

→ A number

COUNTROWS

Count rows in a table

COUNTROWS(Sales)

→ A number

Prefer COUNTROWS over COUNT when you just want the row count.

DISTINCTCOUNT

Count distinct values

DISTINCTCOUNT(Sales[CustomerID])

→ A number

COUNTBLANK

Count blank values

COUNTBLANK(Sales[Notes])

→ A number

Filter context

CALCULATE is the engine of DAX. It evaluates an expression after changing the filter context, and it forces context transition — turning the current row into an equivalent filter. Almost every non-trivial measure goes through it.

CALCULATE

Evaluate with modified filters

CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")

→ A number

The single most important function in DAX. Filter arguments override matching filters from the visual.

CALCULATETABLE

Like CALCULATE, returns a table

CALCULATETABLE(Sales, Sales[Year] = 2026)

→ A table

FILTER

Return rows matching a condition

FILTER(Sales, Sales[Amount] > 1000)

→ A table

Iterates row by row. Inside CALCULATE prefer a simple boolean filter when you can — FILTER is slower.

ALL

Ignore filters on a table/column

CALCULATE(SUM(Sales[Amount]), ALL(Sales))

→ Grand total

The classic denominator for a percent-of-total measure.

ALLEXCEPT

Ignore all filters except listed

CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Region]))

→ A number

ALLSELECTED

Respect slicer/outer selection

CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Sales[Region]))

→ A number

Use for percent-of-visible-total so slicers still apply.

REMOVEFILTERS

Clear filters (modern ALL)

CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales[Region]))

→ A number

KEEPFILTERS

Add a filter without overriding

CALCULATE([Sales], KEEPFILTERS(Sales[Region] = "West"))

→ A number

Intersects with existing context instead of replacing it.

VALUES

Distinct values as a table

VALUES(Sales[Region])

→ A one-column table

SELECTEDVALUE

The single value in context

SELECTEDVALUE(Sales[Region], "All regions")

→ A value

Returns the fallback when zero or many values are in context. Cleaner than HASONEVALUE plus VALUES.

HASONEVALUE

True if exactly one value

HASONEVALUE(Sales[Region])

→ true / false

ISFILTERED

True if a column is filtered

ISFILTERED(Sales[Region])

→ true / false

ISINSCOPE

True if a column is a grouping level

ISINSCOPE(Sales[Region])

→ true / false

The reliable way to detect the current level in a matrix or hierarchy.

Iterators & variables

VAR / RETURN

Store a value for reuse

VAR Total = SUM(Sales[Amount]) RETURN Total * 1.1

→ A value

Variables are evaluated once at their definition point. They improve readability and performance, and they capture the value before later context changes.

RANKX

Rank rows by an expression

RANKX(ALL(Product), [Total Sales])

→ A rank

Wrap the first argument in ALL or ALLSELECTED, or every row ranks 1.

CONCATENATEX

Join an expression across rows

CONCATENATEX(VALUES(Product[Name]), Product[Name], ", ")

→ "A, B, C"

ADDCOLUMNS

Add computed columns to a table

ADDCOLUMNS(VALUES(Region[Name]), "Sales", [Total Sales])

→ A table

SELECTCOLUMNS

Pick and rename columns

SELECTCOLUMNS(Sales, "Region", Sales[Region])

→ A table

SUMMARIZE

Group rows and summarize

SUMMARIZE(Sales, Region[Name], "Sales", SUM(Sales[Amount]))

→ A grouped table

Do aggregations with ADDCOLUMNS over SUMMARIZE, or use SUMMARIZECOLUMNS — plain SUMMARIZE aggregation can misbehave.

SUMMARIZECOLUMNS

Group and summarize (modern, faster)

SUMMARIZECOLUMNS(Region[Name], "Sales", SUM(Sales[Amount]))

→ A grouped table

Time intelligence

These need a dedicated date table that is contiguous (no gaps), covers full years, and is marked as a date table. Without one, they silently return wrong or blank results.

TOTALYTD

Year-to-date total

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

→ A running total

DATESYTD

Year-to-date date set

CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date]))

→ A number

SAMEPERIODLASTYEAR

Shift context back one year

CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))

→ Last year's value

DATEADD

Shift by an interval

CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -1, MONTH))

→ Prior-month value

DATESINPERIOD

A rolling window of dates

CALCULATE(SUM(Sales[Amount]), DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH))

→ Rolling 3-month value

PARALLELPERIOD

A whole shifted period

CALCULATE(SUM(Sales[Amount]), PARALLELPERIOD('Date'[Date], -1, YEAR))

→ A number

PREVIOUSMONTH

The prior month

CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH('Date'[Date]))

→ A number

DATESBETWEEN

An explicit date range

CALCULATE(SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], DATE(2026,1,1), DATE(2026,3,31)))

→ A number

FIRSTDATE / LASTDATE

First/last date in context

LASTDATE('Date'[Date])

→ A date

ENDOFMONTH

Last day of the month in context

ENDOFMONTH('Date'[Date])

→ A date

Logical & error handling

Branch on a condition

IF([Sales] > 0, "Yes", "No")

→ One of two values

SWITCH

Match one value against many

SWITCH([Grade], "A", 4, "B", 3, 0)

→ The matched result

SWITCH(TRUE())

If / else-if chain

SWITCH(TRUE(), [Margin] > 0.3, "High", [Margin] > 0.1, "Mid", "Low")

→ First true branch

The idiomatic way to write nested conditions in DAX.

AND &&

All conditions true

[Active] && [Approved]

→ true / false

AND() takes only two arguments; the && operator chains more cleanly.

OR ||

Any condition true

[IsAdmin] || [IsOwner]

→ true / false

NOT

Invert a condition

NOT(ISBLANK([Sales]))

→ true / false

DIVIDE

Safe division

DIVIDE([Profit], [Sales], 0)

→ A number

Returns the third argument (or blank) on divide-by-zero instead of an error. Always prefer it over the / operator.

COALESCE

First non-blank value

COALESCE([Sales], 0)

→ A value

Cleaner than IF(ISBLANK([Sales]), 0, [Sales]).

IFERROR

Catch and replace an error

IFERROR([Ratio], 0)

→ A value

ISBLANK

Test for blank

ISBLANK([Sales])

→ true / false

BLANK

Return an explicit blank

IF([Sales] = 0, BLANK(), [Sales])

→ A blank

Returning BLANK() hides a row or value in most visuals.

Relationships

Pull a value across a many-to-one relationship

RELATED(Product[Category])

→ A value

Used in a calculated column on the many side, reaching to the one side.

RELATEDTABLE

Get related rows (one-to-many)

COUNTROWS(RELATEDTABLE(Sales))

→ A table / count

USERELATIONSHIP

Activate an inactive relationship

CALCULATE([Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date]))

→ A number

Only works inside CALCULATE. Lets one fact table use a second date column.

CROSSFILTER

Set cross-filter direction

CALCULATE([Sales], CROSSFILTER(Sales[CustID], Customer[CustID], BOTH))

→ A number

LOOKUPVALUE

Look up a value with no relationship

LOOKUPVALUE(Rate[USD], Rate[Date], Sales[Date])

→ A value

TREATAS

Apply one table as a filter on another

CALCULATE([Sales], TREATAS(VALUES(Budget[Region]), Sales[Region]))

→ A number

A virtual relationship — filters across tables that are not physically related.

Text

FORMAT

Format a value as text

FORMAT([Sales], "#,##0.00")

→ "1,234.50"

FORMAT returns text, which sorts alphabetically. Keep the numeric measure for sorting.

CONCATENATE &

Join text values

"Region: " & SELECTEDVALUE(Region[Name])

→ Joined text

CONCATENATE() takes two arguments; the & operator chains more.

LEFT / RIGHT

Characters from one end

LEFT([Code], 3)

→ A substring

MID

Characters from a position

MID([Code], 4, 2)

→ A substring

LEN

Count characters

LEN([Code])

→ A number

UPPER / LOWER

Change case

UPPER([Code])

→ Text

TRIM

Remove extra spaces

TRIM([Name])

→ Trimmed text

SUBSTITUTE

Replace text by match

SUBSTITUTE([Code], "-", "")

→ Text

SEARCH / FIND

Position of a substring

SEARCH("@", [Email], 1, BLANK())

→ A number

SEARCH is case-insensitive; FIND is case-sensitive. Pass a fourth argument to avoid an error when not found.

VALUE

Text to number

VALUE([CodeText])

→ A number

Math & statistics

ROUND

Round to N decimals

ROUND([Sales], 2)

→ A number

ROUNDUP / ROUNDDOWN

Round away from / toward zero

ROUNDUP([Sales], 0)

→ A number

INT

Truncate to an integer

INT([Value])

→ A whole number

ABS

Absolute value

ABS([Variance])

→ A number

MOD

Remainder after division

MOD([Row], 2)

→ A number

CEILING / FLOOR

Round to a multiple

CEILING([Price], 0.05)

→ A number

MEDIAN

Median of a column

MEDIAN(Sales[Amount])

→ A number

PERCENTILE.INC

Percentile of a column

PERCENTILE.INC(Sales[Amount], 0.9)

→ A number

RANK.EQ

Rank a value within a column

RANK.EQ([Sales], ALL(Sales[Amount]))

→ A rank

Tables & set operations

These return tables. Use them as the table argument of an iterator or filter, or inside CALCULATETABLE — you cannot put a table directly in a card visual.

TOPN

Top N rows by an expression

TOPN(5, Product, [Total Sales], DESC)

→ A table

UNION

Stack rows from tables

UNION(Actuals, Budget)

→ A table

INTERSECT

Rows present in both tables

INTERSECT(VALUES(A[ID]), VALUES(B[ID]))

→ A table