DAX Cheat Sheet

50 essentials with syntax and working examples. Search or filter by group.

SUM

Adds all numbers in a column, respecting the current filter context.

SUM(<column>)
Total Sales = SUM(Sales[net_sales])

AVERAGE

Arithmetic mean of a numeric column (blanks are ignored).

AVERAGE(<column>)
Avg Line Sales = AVERAGE(Sales[net_sales])

MIN / MAX

Smallest or largest value in a column. Also works with two scalar arguments: MAX(a, b).

MIN(<column>) | MAX(<column>)
Last Sale Date = MAX(Sales[order_date])
First Sale Date = MIN(Sales[order_date])

COUNT

Counts non-blank numeric or date values in a column.

COUNT(<column>)
Lines With Qty = COUNT(Sales[quantity])

COUNTROWS

Counts the rows of a table (or of the filtered table). The usual way to count fact rows.

COUNTROWS([<table>])
Sales Lines = COUNTROWS(Sales)

DISTINCTCOUNT

Counts the distinct values of a column, e.g. number of orders or of unique customers.

DISTINCTCOUNT(<column>)
Orders = DISTINCTCOUNT(Sales[order_id])
Active Customers = DISTINCTCOUNT(Sales[customer_id])

COUNTBLANK

Counts blank cells in a column. Useful for data-quality checks.

COUNTBLANK(<column>)
Missing Region = COUNTBLANK(Stores[region])

SUMX

Evaluates an expression row by row over a table and adds the results. Use it when you must calculate before summing.

SUMX(<table>, <expression>)
Gross Sales = SUMX(Sales, Sales[quantity] * Sales[unit_price])

AVERAGEX

Averages an expression evaluated for each row of a table. Pass VALUES(...) to average per item, e.g. per order.

AVERAGEX(<table>, <expression>)
Avg Order Value =
AVERAGEX(
    VALUES(Sales[order_id]),
    CALCULATE(SUM(Sales[net_sales]))
)

MAXX / MINX

Largest or smallest value of an expression evaluated over each row of a table.

MAXX(<table>, <expression>) | MINX(<table>, <expression>)
Best Store Sales =
MAXX(
    VALUES(Stores[store_name]),
    [Total Sales]
)

COUNTX

Counts rows where the expression returns a non-blank value. Combine with FILTER for conditional counts.

COUNTX(<table>, <expression>)
Discounted Lines =
COUNTX(
    FILTER(Sales, Sales[discount] > 0),
    Sales[order_item_id]
)

CONCATENATEX

Joins the text of an expression for every row into one string. Great for dynamic titles.

CONCATENATEX(<table>, <expression>, [<delimiter>], [<orderBy>], [<order>])
Selected Regions =
CONCATENATEX(
    VALUES(Stores[region]),
    Stores[region],
    ", ",
    Stores[region], ASC
)

CALCULATE

Evaluates an expression in a modified filter context. The most important function in DAX.

CALCULATE(<expression>, <filter1>, [<filter2>], ...)
South Sales =
CALCULATE(
    [Total Sales],
    Stores[region] = "South"
)

FILTER

Returns the rows of a table that meet a condition. Use it inside CALCULATE when the condition involves a measure or several columns.

FILTER(<table>, <condition>)
Sales Big Stores =
CALCULATE(
    [Total Sales],
    FILTER(
        VALUES(Stores[store_name]),
        [Total Sales] > 21000
    )
)

ALL

Removes filters from a table or columns. Classic use: the denominator of a percent-of-total.

ALL([<table> | <column>, ...])
% of All Regions =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALL(Stores[region]))
)

ALLEXCEPT

Removes all filters from a table except those on the listed columns.

ALLEXCEPT(<table>, <column1>, [<column2>], ...)
Region Total =
CALCULATE(
    [Total Sales],
    ALLEXCEPT(Stores, Stores[region])
)

ALLSELECTED

Removes filters coming from the visual (rows/columns) but keeps slicer and page filters. Gives a visual total.

ALLSELECTED([<table> | <column>, ...])
% of Visible Total =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALLSELECTED(Stores[store_name]))
)

REMOVEFILTERS

Same effect as ALL used as a CALCULATE modifier, but with a clearer name. Cannot be used as a table expression.

REMOVEFILTERS([<table> | <column>, ...])
All Stores Sales =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(Stores)
)

KEEPFILTERS

Makes a CALCULATE filter intersect with the existing filter instead of replacing it.

KEEPFILTERS(<filter>)
Online Sales (kept) =
CALCULATE(
    [Total Sales],
    KEEPFILTERS(Sales[channel] = "Online")
)

VALUES

Returns the distinct values of a column visible in the current filter context (may include a blank row from relationships).

VALUES(<column> | <table>)
Regions Shown = COUNTROWS(VALUES(Stores[region]))

DISTINCT

Distinct values of a column, without the extra blank row VALUES can add for unmatched relationships.

DISTINCT(<column>)
Categories Sold = COUNTROWS(DISTINCT(Products[category]))

SELECTEDVALUE

Returns the value when exactly one value is filtered; otherwise returns the alternate result. Perfect for slicer-driven titles.

SELECTEDVALUE(<column>, [<alternateResult>])
Region Title =
"Sales - " & SELECTEDVALUE(Stores[region], "All regions")

HASONEVALUE

TRUE when the column has exactly one distinct value in the current context. Often used to hide totals.

HASONEVALUE(<column>)
Store Sales (no total) =
IF(HASONEVALUE(Stores[store_name]), [Total Sales])

ISFILTERED

TRUE when the column is directly filtered (by a slicer, visual or filter pane).

ISFILTERED(<column> | <table>)
Region Filter On = IF(ISFILTERED(Stores[region]), "Filtered", "All")

TOTALYTD

Year-to-date total. Needs a marked date table with continuous dates.

TOTALYTD(<expression>, <dates>, [<filter>], [<year_end_date>])
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])

DATESYTD

Returns the dates from the start of the year to the last visible date; use inside CALCULATE. The optional year-end supports fiscal years.

DATESYTD(<dates>, [<year_end_date>])
Sales FYTD (June) =
CALCULATE([Total Sales], DATESYTD('Date'[Date], "6-30"))

TOTALMTD / TOTALQTD

Month-to-date and quarter-to-date totals.

TOTALMTD(<expression>, <dates>) | TOTALQTD(<expression>, <dates>)
Sales MTD = TOTALMTD([Total Sales], 'Date'[Date])
Sales QTD = TOTALQTD([Total Sales], 'Date'[Date])

SAMEPERIODLASTYEAR

Shifts the visible dates back one year. The base of year-over-year comparisons.

SAMEPERIODLASTYEAR(<dates>)
Sales LY =
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])

DATEADD

Shifts the visible dates by a number of intervals (negative = back in time).

DATEADD(<dates>, <number>, DAY | MONTH | QUARTER | YEAR)
Sales Prev Month =
CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH))

PARALLELPERIOD

Returns the full parallel period (whole month/quarter/year), shifted by the given number of intervals.

PARALLELPERIOD(<dates>, <number>, MONTH | QUARTER | YEAR)
Sales Full Prev Year =
CALCULATE([Total Sales], PARALLELPERIOD('Date'[Date], -1, YEAR))

PREVIOUSMONTH

All dates of the month before the first visible date.

PREVIOUSMONTH(<dates>) (also PREVIOUSDAY, PREVIOUSQUARTER, PREVIOUSYEAR)
Sales Last Month =
CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))

DATESINPERIOD

Dates in a window starting at a date and extending N intervals. Used for rolling totals.

DATESINPERIOD(<dates>, <start_date>, <number>, DAY | MONTH | QUARTER | YEAR)
Sales Rolling 3M =
CALCULATE(
    [Total Sales],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH)
)

DATESBETWEEN

Dates between two dates, inclusive.

DATESBETWEEN(<dates>, <start_date>, <end_date>)
Sales H1 2025 =
CALCULATE(
    [Total Sales],
    DATESBETWEEN('Date'[Date], DATE(2025, 1, 1), DATE(2025, 6, 30))
)

RELATED

Brings a value from the 'one' side of a relationship into a row of the 'many' side (calculated columns or iterators).

RELATED(<column>)
Gross Margin =
SUMX(
    Sales,
    Sales[net_sales] - Sales[quantity] * RELATED(Products[unit_cost])
)

RELATEDTABLE

Returns the rows of the 'many' side related to the current row of the 'one' side.

RELATEDTABLE(<table>)
-- calculated column in Stores
Store Lines = COUNTROWS(RELATEDTABLE(Sales))

USERELATIONSHIP

Activates an inactive relationship for one calculation (inside CALCULATE).

USERELATIONSHIP(<column1>, <column2>)
-- inactive relationship: Customers[signup_date] -> 'Date'[Date]
New Customers =
CALCULATE(
    COUNTROWS(Customers),
    USERELATIONSHIP(Customers[signup_date], 'Date'[Date])
)

CROSSFILTER

Changes the filter direction of a relationship for one calculation, e.g. to make Products filter Customers through Sales.

CROSSFILTER(<column1>, <column2>, NONE | ONEWAY | BOTH)
Customers Who Bought =
CALCULATE(
    COUNTROWS(Customers),
    CROSSFILTER(Sales[customer_id], Customers[customer_id], BOTH)
)

IF

Returns one value or another depending on a condition. Without the third argument it returns BLANK.

IF(<condition>, <value_if_true>, [<value_if_false>])
Target Status =
IF([Total Sales] >= 20000, "On target", "Below target")

SWITCH

Compares an expression with a list of values. With SWITCH(TRUE(), ...) it works like a chain of IFs.

SWITCH(<expression>, <value1>, <result1>, ..., [<else>])
Sales Band =
SWITCH(
    TRUE(),
    [Total Sales] >= 21000, "High",
    [Total Sales] >= 20000, "Medium",
    "Low"
)

AND / OR / NOT (&&, ||)

Combine conditions. AND() and OR() take only two arguments; the && and || operators can be chained.

<cond1> && <cond2> | <cond1> || <cond2> | NOT(<cond>)
South Supermarket Online =
CALCULATE(
    [Total Sales],
    Stores[region] = "South" && Stores[store_type] = "Supermarket",
    Sales[channel] = "Online"
)

ISBLANK / COALESCE

ISBLANK tests for BLANK; COALESCE returns the first non-blank argument (handy to show 0 instead of blank).

ISBLANK(<value>) | COALESCE(<value1>, <value2>, ...)
Sales or Zero = COALESCE([Total Sales], 0)
No Sales Flag = IF(ISBLANK([Total Sales]), "No sales")

FORMAT

Converts a value to text with a number or date format. The result is text, so don't use it for numbers you need to sort or sum.

FORMAT(<value>, <format_string>, [<locale>])
Sales Label = FORMAT([Total Sales], "$#,##0")
Month Name = FORMAT('Date'[Date], "mmm yyyy")

& / CONCATENATE

Joins text. CONCATENATE takes only two arguments, so the & operator is usually easier.

<text1> & <text2> | CONCATENATE(<text1>, <text2>)
-- calculated column in Stores
Store Label = Stores[store_name] & " (" & Stores[region] & ")"

DIVIDE

Safe division: returns BLANK (or the alternate result) instead of an error when the denominator is 0 or blank.

DIVIDE(<numerator>, <denominator>, [<alternateResult>])
Margin % = DIVIDE([Total Sales] - [Total Cost], [Total Sales])

VAR / RETURN

Stores intermediate results in variables. Code is easier to read and each variable is calculated only once.

VAR <name> = <expression> RETURN <result>
YoY % =
VAR CurrentSales = [Total Sales]
VAR LastYear =
    CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    DIVIDE(CurrentSales - LastYear, LastYear)

RANKX

Ranks each item by an expression. Use ALL(...) on the table so every item is compared with all the others.

RANKX(<table>, <expression>, [<value>], [ASC | DESC], [SKIP | DENSE])
Store Rank =
RANKX(
    ALL(Stores[store_name]),
    [Total Sales],
    ,
    DESC,
    DENSE
)

TOPN

Returns the top N rows of a table by an expression (ties may return more rows). Often used as a CALCULATE filter.

TOPN(<n>, <table>, <orderBy>, [ASC | DESC])
Top 5 Products Sales =
CALCULATE(
    [Total Sales],
    TOPN(5, ALL(Products[product_name]), [Total Sales], DESC)
)

SUMMARIZECOLUMNS

Builds a grouped summary table. The standard way to write queries (DAX query view) and calculated tables.

SUMMARIZECOLUMNS(<groupBy_column>, ..., [<filter>], "<name>", <expression>, ...)
EVALUATE
SUMMARIZECOLUMNS(
    Stores[region],
    'Date'[Year],
    "Sales", [Total Sales],
    "Orders", DISTINCTCOUNT(Sales[order_id])
)

CALENDAR

Creates a one-column table of continuous dates. The usual start of a date table (CALENDARAUTO detects the range for you).

CALENDAR(<start_date>, <end_date>)
Date = CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31))

ADDCOLUMNS

Adds calculated columns to a table expression. Combined with CALENDAR it builds a complete date table.

ADDCOLUMNS(<table>, "<name>", <expression>, ...)
Date =
ADDCOLUMNS(
    CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR([Date]),
    "Month Num", MONTH([Date]),
    "Month", FORMAT([Date], "mmm"),
    "Quarter", "Q" & QUARTER([Date])
)