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 section

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 function 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.
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.