Microsoft Office Tutorials and References
In Depth Information
In other words, the condition that filters only cities in Italy or France operates on all the values
of the Country column. In fact, it always returns a value, regardless of the user selection of
Country. If you want to consider the current filter context and further restrict it by adding
another filter condition without replacing the current selection, you need to use the VALUES
function instead of the ALL one.
CALCULATE( SUM( Orders[Amount] ),
FILTER( VALUES( Cities[Country] ),
OR( Cities[Country] = "Italy",
Cities[Country] = "France" ) ) )
VALUES returns a one-column table that contains the distinct values from the specified
column (duplicates are eliminated) that are active in the current filter context. It is
equivalent to the DISTINCT function, which has exactly the same behavior.
Avoid using FILTER in CALCuLATE
Sometimes you can use alternatives to FILTER in a CALCULATE call. Whenever you have
an alternative to FILTER (for example, by using a Boolean condition on a column), use
The FILTER function creates a new table in memory, whereas a simple filter condition on
a column does not require this and is faster than the FILTER function.
Final Considerations for Evaluation Context
Filter context and row context are two important concepts that you need to understand well
to create advanced DAX expressions.
This is a short list of the more important concepts that you have learned until now about
■ RELATED always refers to the row context.
■ Column references with the syntax Table[Column] always refer to the row context.
■ CALCULATE and RELATEDTABLE convert all row contexts to filter context.
■ VALUES is bound to filter context.
■ Base tables used as arguments for aggX and FILTER functions are filtered by filter
■ Use CALCULATE instead of FILTER or RELATEDTABLE to get better performance.