Microsoft Office Tutorials and References
In Depth Information
the value of the QtyGreen measure. The reason you lose the current selection on the color
attribute is that the ALL( Demo[Color] ) expression returns a set of all the color values and
ignores the existing selection.
If you do not want to lose the existing selection of the PivotTable (which means that you do not
want to lose the existing filters on the calculation context), you can simply use a function that
takes the existing selection into account. So instead of using the ALL( Demo[Color] ) expression,
you can use VALUES( Demo[Color] ), which keeps existing selections and returns only the values
still available in the color attribute.
In fact, if you create a QtyGreenFiltered measure with the following expression:
CALCULATE( SUM( Orders[Quantity] ),
FILTER( VALUES( Orders[Color]), Orders[Color] = "Green" ) )
the Color filter of the PivotTable is still active, and the QtyGreenFiltered column now correctly
computes no value for the Red rows, as you can see in Figure 6-33.
FIguRE 6-33 The effects of using FILTER( VALUES .. ) in CALCULATE to keep existing selections.
The FILTER expression in a CALCULATE function always replaces the previous context for the
referenced columns. However, you can save the existing context by using an expression that
uses the existing context and further restricts the members you want to consider for one or
more columns. This is what you did when you used the VALUES function instead of ALL as
the first parameter of the FILTER call.
At this point, you can summarize the effect of the various combination of FILTER, ALL, and
VALUES in a CALCULATE statement.
CALCULATE( SUM( Orders[Quantity] ),
Orders[Color] = "Green" )
CALCULATE( SUM( Orders[Quantity] ),
FILTER( ALL( Orders[Color] ), Orders[Color] = "Green" ) )
Search JabSto ::

Custom Search