Filter Functions
The filter and value functions in DAX are some of the most complex and powerful, and differ greatly from Excel functions. The lookup functions work by using tables and relationships, like a database. The filtering functions let you manipulate data context to create dynamic calculations.
In this category
Function | Description |
---|---|
ADDMISSINGITEMS | Adds combinations of items from multiple columns to a table if they do not already exist. |
ALL | Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. |
ALLCROSSFILTERED | Clear all filters which are applied to a table. |
ALLEXCEPT | Removes all context filters in the table except filters that have been applied to the specified columns. |
ALLNOBLANKROW | From the parent table of a relationship, returns all rows but the blank row, or all distinct values of a column but the blank row, and disregards any context filters that might exist. |
ALLSELECTED | Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters. |
CALCULATE | Evaluates an expression in a context that is modified by the specified filters. |
CALCULATETABLE | Evaluates a table expression in a context modified by the given filters. |
CROSSFILTER | Specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns. |
DISTINCT | Returns a one-column table that contains the distinct values from the specified column. |
EARLIER | Returns the current value of the specified column in an outer evaluation pass of the mentioned column. |
EARLIEST | Returns the current value of the specified column in an outer evaluation pass of the specified column. |
FILTER | Returns a table that represents a subset of another table or expression. |
FILTERS | Returns the values that are directly applied as filters to columnName. |
HASONEFILTER | Returns TRUE when the number of directly filtered values on columnName is one; otherwise returns FALSE. |
HASONEVALUE | Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE. |
ISCROSSFILTERED | Returns TRUE when columnName or another column in the same or related table is being filtered. |
ISFILTERED | Returns TRUE when columnName is being filtered directly. |
KEEPFILTERS | Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function. |
RELATED | Returns a related value from another table. |
RELATEDTABLE | Evaluates a table expression in a context modified by the given filters. |
REMOVEFILTERS | Clears filters from the specified tables or columns. |
SELECTEDVALUE | Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult. |
SUBSTITUTEWITHINDEX | Returns a table which represents a left semijoin of the two tables supplied as arguments. |
USERELATIONSHIP | Specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2. |
VALUES | Returns a one-column table that contains the distinct values from the specified table or column. |