Microsoft Office Tutorials and References
In Depth Information
In Figure 6-25, you can see that the filter made on the Europe Continent by the EuropeSales
measure returns data only from France: the filter made on the Continent Slicer is ignored for
that measure, but the filter on Country is still valid and removes the Italy rows of the Orders
table from the filter context.
The second type of filter parameter for the CALCULATE function is a list of values for a column
or a list of rows for a table. For example, you could get the total Amount for all the Continents
using this expression (we call it AllContinents in Figure 6-26):
CALCULATE( SUM( Orders[Amount] ), ALL( Cities[Continent] ) )
FIguRE 6-26 The PivotTable excludes Orders from Italy and Spain, despite ignoring the Continent selections.
However, even in this case the measure ignores any selections in the Continent Slicer, but it
makes use of the Country Slicer selection in the filter context to calculate the sum of Amount,
as you can see in Figure 6-26.
Important Actually, the user has to select an existing combination of Continent and Country
in the slicers. For example, if you select USA and Europe, there are no valid rows in the Cities
table; this stops the calculation for Orders and shows no data at all. This limitation is valid for
all the following examples.
If you wanted to ignore any of the selections in the Cities attributes (which are Continent,
Country, and City), you have to write this other expression:
CALCULATE( SUM( Orders[Amount] ), ALL( Cities ) )
As you can see in Figure 6-27, the new AllCities measure ignores any selections made by
Country and Continent Slicers.