Edit

Share via

Filter functions

  • Article

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
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 modified filter context.
CALCULATETABLE Evaluates a table expression in a modified filter context.
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.
FIRST Used in visual calculations only. Retrieves a value in the visual matrix from the first row of an axis.
INDEX Returns a row at an absolute position, specified by the position parameter, within the specified partition, sorted by the specified order or on the specified axis.
KEEPFILTERS Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.
LAST Used in visual calculations only. Retrieves a value in the visual matrix from the last row of an axis.
LOOKUPVALUE Returns the value for the row that meets all criteria specified by search conditions. The function can apply one or more search conditions.
MATCHBY In window functions, defines the columns that are used to determine how to match data and identify the current row.
MOVINGAVERAGE Returns a moving average calculated along the given axis of the visual matrix.
NEXT Used in visual calculations only. Retrieves a value in the next row of an axis in the visual matrix.
OFFSET Returns a single row that is positioned either before or after the current row within the same table, by a given offset.
ORDERBY Defines the columns that determine the sort order within each of a window function’s partitions.
PARTITIONBY Defines the columns that are used to partition a window function’s <relation> parameter.
PREVIOUS Used in visual calculations only. Retrieves a value in the previous row of an axis in the visual matrix.
RANGE Returns an interval of rows within the given axis, relative to the current row. A shortcut for WINDOW.
RANK Returns the ranking of a row within the given interval.
REMOVEFILTERS Clears filters from the specified tables or columns.
ROWNUMBER Returns the unique ranking of a row within the given interval.
RUNNINGSUM Returns a running sum calculated along the given axis of the visual matrix.
SELECTEDVALUE Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.
WINDOW Returns multiple rows which are positioned within the given interval.