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