This reference lists 18 DAX filter functions, with frequently used functions marked 🔥. Each function includes syntax, an example and parameter notes.
| Function | Description | Syntax | Example |
|---|---|---|---|
ALL 🔥 |
Returns all rows ignoring the filter context, or all columns except the specified ones | ALL(<tableOrColumn>) |
CALCULATE(SUM(Sales[Amount]), ALL(Sales[Region])) |
CALCULATE 🔥 |
Evaluates an expression in a modified filter context | CALCULATE(<expression>, [<filter1>], [<filter2>], ...) |
CALCULATE(SUM(Sales[Amount]), Sales[Region] = "North") |
CALCULATETABLE 🔥 |
Evaluates a table expression in a modified filter context | CALCULATETABLE(<expression>, [<filter1>], [<filter2>], ...) |
CALCULATETABLE(Sales, Sales[Region] = "North") |
FILTER 🔥 |
Returns a filtered table | FILTER(<table>, <filter>) |
FILTER(Sales, Sales[Amount] > 1000) |
HASONEVALUE 🔥 |
Checks whether the column has exactly one unique value | HASONEVALUE(<columnName>) |
IF(HASONEVALUE(Sales[Region]), VALUES(Sales[Region]), "Multiple regions") |
LOOKUPVALUE 🔥 |
Looks up a value in a table that satisfies the search conditions | LOOKUPVALUE(<result_columnName>, <search_columnName>, <search_value>) |
LOOKUPVALUE(Products[ProductName], Products[ProductID], Sales[ProductID]) |
SELECTEDVALUE 🔥 |
Returns the value of a column when it has exactly one value; otherwise returns the alternate result | SELECTEDVALUE(<column>, [<alternateResult>]) |
SELECTEDVALUE(Sales[Region], "All") |
ALLEXCEPT |
Returns all filtered rows in the context except those filtered by the specified columns | ALLEXCEPT(<table>, <column>) |
CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Region])) |
ALLSELECTED |
Returns all rows except the current row and column contexts | ALLSELECTED([<tableOrColumn>]) |
CALCULATE(SUM(Sales[Amount]), ALLSELECTED()) |
EARLIER |
Returns the value of a column in an earlier (outer) context than the current one | EARLIER(<column>, [<number>]) |
COUNTROWS(FILTER(Sales, Sales[Amount] > EARLIER(Sales[Amount]))) |
EARLIEST |
Returns the value of a column in the outermost context | EARLIEST(<column>) |
CALCULATE(SUM(Sales[Amount]), EARLIEST(Sales[Date])) |
FILTERS |
Returns the filters applied in the current context | FILTERS(<columnName>) |
FILTERS(Sales[Region]) |
ISCROSSFILTERED |
Checks whether the column is cross-filtered | ISCROSSFILTERED(<columnName>) |
ISCROSSFILTERED(Sales[ProductID]) |
ISFILTERED |
Checks whether the column is filtered directly | ISFILTERED(<columnName>) |
IF(ISFILTERED(Sales[Region]), "Filtered", "Not filtered") |
KEEPFILTERS |
Keeps existing filters while applying new ones | KEEPFILTERS(<expression>) |
CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Sales[Region] = "North")) |
LOOKUP |
Looks up a value with filters applied, in visual calculations | LOOKUP(<expression>, [<filter>]) |
LOOKUP(SUM(Sales[Amount])) |
LOOKUPWITHTOTALS |
Looks up a value with filters applied, in visual calculations | LOOKUPWITHTOTALS(<expression>, [<filter>]) |
LOOKUPWITHTOTALS(SUM(Sales[Amount])) |
REMOVEFILTERS |
Clears filters from the specified table or columns | REMOVEFILTERS([<tableOrColumn>]) |
CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales[Region])) |
Detailed parameters for frequently used functions
ALL
Returns all rows ignoring the filter context, or all columns except the specified ones.
| Parameter | Description |
|---|---|
tableOrColumn |
The table or column to return. If a column is passed, returns all of its unique values. |
CALCULATE
Evaluates an expression in a modified filter context.
| Parameter | Description |
|---|---|
expression |
The expression to evaluate in the modified filter context. |
filter1, filter2... |
(Optional) Filters to apply. Can be boolean expressions, table expressions or filter functions. |
CALCULATETABLE
Evaluates a table expression in a modified filter context.
| Parameter | Description |
|---|---|
expression |
The table expression to evaluate in the modified filter context. |
filter1, filter2... |
(Optional) Filters to apply. |
FILTER
Returns a filtered table.
| Parameter | Description |
|---|---|
table |
The table (or table expression) to filter. |
filter |
A boolean expression that returns TRUE or FALSE, evaluated for each row of the table. |
HASONEVALUE
Checks whether the column has exactly one unique value.
| Parameter | Description |
|---|---|
columnName |
The name of the column to check. |
LOOKUPVALUE
Looks up a value in a table that satisfies the search conditions.
| Parameter | Description |
|---|---|
result_columnName |
The column containing the value to return. It must be in the same table. |
search_columnName |
The column to search the value in. |
search_value |
The value to search for. |
SELECTEDVALUE
Returns the value of a column when it has exactly one value; otherwise returns the alternate result.
| Parameter | Description |
|---|---|
column |
The column to check. |
alternateResult |
(Optional) The result to return when the column has zero or multiple values. Defaults to BLANK. |
From Online Toolbox (www.vba.net)