Microsoft Office Tutorials and References
In Depth Information
The preceding syntaxes are equivalent and both clear the existing Color filter and then set a
filter on the Green Color.
CALCULATE( SUM( Orders[Quantity] ),
FILTER( VALUES( Orders[Color] ), Orders[Color] = "Green" ) )
The preceding syntax keeps the existing Color filters by adding a further filter on Green.
CALCULATE( SUM( Orders[Quantity] ),
FILTER( ALL( Orders[Color] ), Orders[Color] = "Green" ),
VALUES( Orders[Color] ) )
This syntax is like the one that precedes it and keeps existing Color filters by adding a further
filter on Green. Note that the first filter (FILTER( ALL … )) would consider all the colors, but
the second expression (VALUES) considers only the current selection of colors. The two
filters work by using an AND condition, and the final result is the same as if you just used
VALUES instead of ALL in the first parameter of the FILTER call, as in the previous syntax.
CALCULATE( SUM( Orders[Quantity] ),
FILTER( ALL( Orders ), Orders[Color] = "Green" ) )
This new syntax clears existing filters on all the columns of the Orders table, and then sets a
filter on all the columns (City, Channel, Size, and so on, not just Color) by using the rows of
the Orders table that meet the filter condition (in this case, the rows that are Green).
Important Notice that a filter parameter for CALCULATE has a slightly different behavior if it
represents a set of members of a single column or a set of rows from a table. In this last case, any
column filter in the same table existing in the filter context before the CALCULATE call is cleared
by the presence of this set of rows. However, there could be other filter parameters that specify
other conditions for rows and/or columns of the same table. If the same column or the same
table is filtered in more parameters of the same CALCULATE call, the resulting filter context
contains only rows that satisfy all of the filter conditions.
Finally, let us caution you about the first parameter you pass to the FILTER function. If you
consider this possible definition of the QtyGreen measure
CALCULATE( SUM( Orders[Quantity] ),
FILTER( Orders, Orders[Color] = "Green" ) )
you pass the whole Orders table to the FILTER condition, which results in a filter of the filter
context with all the columns of the Orders table! Accordingly, you apply a restriction on the
color Green and you get the same result as before (no rows for any color but green; the
selection of color of the PivotTable is still applied so that row for the color Red returns an
empty cell), but remember, the FILTER is returning all the columns. What does this mean?
Search JabSto ::

Custom Search