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.

***** Related Support Forum Posts*****
Mastering DAX Calculations: What Is DAX?
Understanding DAX Formula
Developing Complex Dax functions

Filter Functions

In this category

DOWNLOAD

Erol Yücel

Mahesh T, London

Geoff Ellis

Matt Ballinger

Sourabh

Brian Marron