Microsoft Office Tutorials and References
In Depth Information
Modifying Filter Context for Multiple Tables
We previously introduced the CALCULATE function to remove filters from filter context for a single
table. However, CALCULATE is the most important function for manipulating the filter context.
The CALCULATE function accepts two forms of filter parameters. The first type is a Boolean
expression, similar to a filter condition in a FILTER function. This type of filter for CALCULATE
is also called table filter . For example, this expression calculates the total amount for Europe,
regardless of the selection in the Continent Slicer (we call it EuropeSales in Figure 6-25):
CALCULATE( SUM( Orders[Amount] ), Cities[Continent] = "Europe" )
This expression is equivalent to the following:
SUMX( FILTER( Orders, RELATED( Cities[Continent] ) = "Europe" ),
However, it is always better to write the CALCULATE version because it is faster than the SUMX
version. In general, whenever you can write an expression using CALCULATE or FILTER, always
prefer CALCULATE instead of FILTER for performance reasons. This is another reason why
learning CALCULATE is very important.
Note The reason the CALCULATE function is faster than the FILTER function lies in the way the
filter is made in the Orders table.
When you use CALCULATE, the filter is applied to the Cities table, and the real filter of the Orders
table is made by using the City set that belongs to the Europe Continent data in the Cities table.
This operation is pretty fast because it uses internal indexes and does not require a complete
iteration over all the rows of the Orders table.
By using SUMX, the filter on the Orders table is made by the FILTER function, which could iterate
the Orders table row by row, making an evaluation of the filter condition for each Orders row,
without taking advantage of existing internal indexes defined by the relationship between the
Cities and Orders tables.
FIguRE 6-25 The PivotTable calculates only Orders from France, despite the filter on the North America