Microsoft Office Tutorials and References
In Depth Information
FIguRE 6-27 The AllCities measure ignores any selections of Continent and Country Slicers.
The CALCULATE function allows you to define a new filter context based on the existing one.
However, you can completely overwrite the existing filter context if you specify a new filter for
each of the columns of the tables. In fact, you can specify more filters in the same CALCULATE
function call, and you could write such an expression, which ignores any selection of Continent,
Country (both of these belong to the Cities table), and Channel Slicers:
CALCULATE( SUM( Orders[Amount] ), ALL( Cities ), ALL( Channels ) )
All the filter conditions that you specify in a CALCULATE function call have to be satisfied to
make a row active in the filter context. In other words, all the filter conditions are in logical
AND. Usually, mixing ALL on some columns and specifying Boolean conditions for others,
you obtain the filter context that you want.
For example, if you wanted to filter Italy and France only (doing just as you did when you
selected both those countries in the Country Slicer), and you want to ignore the selection
of the City attribute, you can use this expression:
CALCULATE( SUM( Orders[Amount] ),
OR( Cities[Country] = "Italy", Cities[Country] = "France" ),
ALL( Cities[City] ) )
However, sometimes the CALCULATE function syntax cannot be used—for example when
you have an OR condition or when you need to compare more columns at a row level in a
table. Unfortunately, these kinds of filter conditions cannot be used directly in a CALCULATE
filter. For example, the following expression generates the error shown in Figure 6-28.
CALCULATE( SUM( Orders[Amount] ),
Orders[Quantity] * 2 < Orders[Price] )
Search JabSto ::

Custom Search