Microsoft Office Tutorials and References
In Depth Information
FIguRE 6-10 The cell F5 is empty because the CalcAmount measure considers only rows with a price
greater than 1.
Removing Filters from a Filter Context for a Single Table
Now that you have seen how to add a filter to the filter context, let us take a look at how to
remove one of the existing filters. If you need to remove a filter from the filter context or if you
want to remove the filter context on a table altogether, you need a slightly different approach.
The first technique is to remove all the filters on a table. For example, if you want to get the
value of all the orders, regardless of any selection made by the user in the PivotTable, you can
use the ALL function, which returns a table containing all the rows of a table, regardless of the
existing filter context, and then passes its result to the SUMX function. For example, you can
create an AllAmount measure by using the following expression:
SUMX( ALL( Orders ), Orders[Amount] )
In Figure 6-11, you can see that for any cell of the PivotTable where AllAmount is calculated,
the value is always the same (it is always 749) because the ALL function ignores the filter
FIguRE 6-11 The AllAmount measure always considers all the rows in the Order table.
Note From a certain point of view, the ALL function does not change the filter context, but it
really creates a new one, at least when it is applied to a table. Shortly, you will see that ALL can
be used also with a single column as parameter to eliminate a filter from a filter context for just