Microsoft Office Tutorials and References
In Depth Information
If the filter context has a filter on a column of a PowerPivot table (which is a selection of a
PivotTable, regardless of whether it is a slicer, a report filter, or a row/column selection), any
reference for that column in one or more filter parameters of the CALCULATE function replaces
the existing filter context for that column. Then the filters specified in the CALCULATE
parameters are combined by using a logical AND condition among them; in other words,
only rows that satisfy all the filter conditions in the filter context are considered.
For instance, consider a filter on the Color Green using a Boolean expression in the
CALCULATE function for the QtyGreen measure:
CALCULATE( SUM( Orders[Quantity] ), Orders[Color] = "Green" )
This is important: a Boolean expression used as a filter parameter in a CALCULATE function
corresponds to an equivalent FILTER expression that operates on all the values of a column:
CALCULATE( SUM( Orders[Quantity] ),
FILTER( ALL( Orders[Color]), Orders[Color] = "Green" ) )
For this reason, only a single column can be specified in a Boolean expression that is used as
a table filter expression in a CALCULATE call.
Now the QtyGreen measure always filters by color Green, and each Channel has only two
rows with the color Green, as shown in Figure 6-32.
FIguRE 6-32 The filter on Color in the QtyGreen measure overrides the row filter context defined by the
PivotTable.
Any filter expression in a CALCULATE statement overrides the existing selection of the
PivotTable for the columns it contains. In the previous rule, we highlighted the reference
definition because the FILTER that is internally used in place of the Boolean expression
uses a FILTER expression that returns a set of values for the Color column. So the existing
selection for the color (the Color is in fact specified in the rows of the PivotTable) is
overridden by your filter and only Green rows in the source table are considered for calculating
Search JabSto ::




Custom Search