Microsoft Office Tutorials and References
In Depth Information
is the other FILTER parameter, which returns all the columns from the Orders table. Consider
the boldface filter in the QtyGreenSmall definition:
CALCULATE( SUM( Orders[Quantity] ),
FILTER( Orders, Orders[Color] = "Green" ),
FILTER( ALL( Orders[Size] ), Orders[Size] = "Small" ) )
The filter on the color Green returns all the columns of the Orders table in the current filter
context. If you consider the corresponding rows for the cell D5 of the PivotTable (<Internet,
Green, Large>), this is just one row (the one with 64 as Quantity), and this row has the Large
value for the Size column. When you apply the second filter, you have a single value for
the attribute Size, which is Small. At this point, the intersection between those two sets
of Size (one is only Large, the other is only Small) is an empty set. So the result for the
QtyGreenSmall measure is empty because there are no corresponding rows for the selection
made, as you saw in Figure 6-35.
This can be tricky, but you finally see this behavior:
■ The CALCULATE function applies a calculation (the first parameter) for each cell,
considering the resulting filter obtained by replacing the filters (the second and following
parameters) of the current filter context.
■ Each filter constraint in a CALCULATE function can be either a table filter constraint or a
column filter constraint.
■ A column filter constraint has values for only one column and is defined by using a
Boolean filter constraint or a function returning a single column table.
■ A table filter constraint can have values for one or more columns and is defined by using
a function returning a table with more than one column.
■ Each filter constraint is computed individually in the filters expressions of the
■ If a column value is specified in at least one filter, it replaces the selection of the filter
context for that column.
■ If a filter expression returns more columns, it is a table filter constraint. The CALCULATE
function considers only table rows that have at least a matching row in the table filter
■ If a column is specified in many filter constraints, the resulting values are the intersection
of these sets of values (for that column).
■ After all the filters have been evaluated, the intersection of all the filter constraints
determines the filter context for the expression passed as the first parameter to the