Filter modifier functions

In the Ultra Transcenders books

DP-600PL-300

Each book explains Filter modifier functions in context, with comparison tables and the common traps.

Terms in this definition

• FILTER

  Returns just those rows of a table that meet a condition. In CALCULATE it handles conditions too complex for a Boolean filter argument, though a Boolean filter is faster whenever one will work.

• DAX

  Short for Data Analysis Expressions, the formula language you write measures, calculated columns and row-level security rules in, within tabular semantic models in Power BI, Excel Power Pivot or Analysis Services.

• Filter context

  Whenever DAX evaluates an expression, each column is limited to certain values by slicers, report filters, a visual's rows and columns, relationships and any filters coded in formulas. Measures always calculate under these limits; row context, by contrast, means the current row.

• CALCULATE

  A DAX function that computes an expression after changing the filter context with filter modifiers, Boolean filters or table filters. Called with no filters, it still turns any row context into filter context, which is known as context transition.

• CALCULATETABLE

  Works like CALCULATE but returns a table: a table expression is evaluated under a modified filter context, with filter arguments behaving exactly as they do for CALCULATE.

• REMOVEFILTERS

  A DAX filter modifier, used within CALCULATE, that clears any filters on given tables or columns. When clearing filters is all you want, it is recommended over ALL.

• ALL

  A DAX function that ignores any filters and gives back every row of a table or every value of the named columns. Used within CALCULATE, it works as a modifier that clears filters, although REMOVEFILTERS states that intent more clearly where it is available.

• KEEPFILTERS

  Wrapping a CALCULATE or CALCULATETABLE filter argument in this DAX modifier stops it overriding filters already on those columns; instead only values satisfying both survive.