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

CALCULATE function.

■
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

constraint.

■
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

CALCULATE function.