DAX Filter Functions

WINDOW(<from>, <to>, <relation>, [<orderBy_expression>], [<blanks>], [<partitionBy_expression>])

Returns multiple rows which are positioned within the given interval

ORDERBY(<orderBy_expression>, [<order>], [<orderBy_expression>], [<order>]…)

Defines the columns that determine the sort order within WINDOW, OFFSET, INDEX and other functions

ALLCROSSFILTERED(<table>)

Clear all filters which are applied to a table

EARLIEST(<column>)

Returns the current value of the specified column in an outer evaluation pass of the mentioned column

ALLNOBLANKROW(<table>|<column>)

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

OFFSET(<delta>, <table>, [<orderBy_expression>], [<order>], [<blanks>], [<partitionBy_expression>])

Returns a single row that is positioned either before or after the current row within the same table

VALUES(<TableNameOrColumnName>)

When the input parameter is a column name, returns a one-column table that contains the distinct values from the specified column

EARLIER(<column>, [<number>])

Returns the current value of the specified column in an outer evaluation pass of the mentioned column

CALCULATETABLE(<expression>, [<filter1>], [<filter2>]…)

Evaluates a table expression in a modified filter context

FILTER(<table>, <filter>)

Returns a table that represents a subset of another table or expression

ALL([<table>|<column>[, <column>[, <column>[,…]]]])

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied

MATCHBY(<column>[, <column>]...)

In window functions, defines the columns that are used to determine how to match data and identify the current row.

ROWNUMBER([<orderBy>], [<partitionBy>], [<matchBy>], [<reset>])

Returns the unique ranking of a row within the given interval.

FIRST(<column>)

Used in visual calculations only. Retrieves a value in the visual matrix from the first row of an axis.

REMOVEFILTERS([<table> | <column>[, <column>[, ...]]])

Clears filters from the specified tables or columns.

PREVIOUS(<column>[, <steps>])

Used in visual calculations only. Retrieves a value in the previous row of an axis in the visual matrix.

PARTITIONBY(<column>[, <column>]...)

Defines the columns that are used to partition a window function's relation parameter.

INDEX(<position>, <orderBy>, [<partitionBy>], [<matchBy>], [<reset>])

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.

LOOKUP(<result_columnName>, <search_columnName>, <search_value>[, <alternate_result>])

In visual calculation mode only. Look up the value when filters applied.

ALLSELECTED([<tableName> | <columnName>[, <columnName>[, <columnName>[,…]]]])

Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

SELECTEDVALUE(<columnName>[, <alternateResult>])

Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.

KEEPFILTERS(<expression>)

Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.

NEXT(<column>[, <steps>])

Used in visual calculations only. Retrieves a value in the next row of an axis in the visual matrix.

MOVINGAVERAGE(<expression>, <windowSize>, <orderBy>, [<partitionBy>], [<matchBy>], [<reset>])

Returns a moving average calculated along the given axis of the visual matrix.

RUNNINGSUM(<expression>, <orderBy>, [<partitionBy>], [<matchBy>], [<reset>])

Returns a running sum calculated along the given axis of the visual matrix.

ALLEXCEPT(<table>, <column>[, <column>[, ...]])

Removes all context filters in the table except filters that have been applied to the specified columns.

LAST(<column>)

Used in visual calculations only. Retrieves a value in the visual matrix from the last row of an axis.

LOOKUPWITHTOTALS(<result_columnName>, <search_columnName>, <search_value>[, <alternate_result>])

In visual calculation mode only. Look up the value when filters applied. Filters not specified will not be inferred.

RANK([<ties>], <expression>[, <expression>]..., [<orderBy>], [<blanks>], [<partitionBy>], [<matchBy>], [<reset>])

Returns the ranking of a row within the given interval.

LOOKUPVALUE(<result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]...[, <alternateResult>])

Returns the value for the row that meets all criteria specified by search conditions. The function can apply one or more search conditions.

RANGE(<from>, <to>[, <step>])

Returns an interval of rows within the given axis, relative to the current row. A shortcut for WINDOW.

CALCULATE(<expression>, <filter1>, <filter2>...)

Evaluates an expression in a modified filter context