Microsoft Office Tutorials and References
In Depth Information
If you need to filter only some rows, but with criteria that are independent of the current
filter context, you can combine FILTER and ALL to get, for example, all the rows in Orders
for the Internet Channel. You can define an AllInternet measure by using the following DAX
expression (the result of which you can see in Figure 6-12):
SUMX( FILTER( ALL( Orders ), Orders[Channel]="Internet" ), Orders[Amount] )
FIguRE 6-12 The AllInternet measure always considers all the rows in the Order table.
In Figure 6-12, the AllInternet value is always 592, which corresponds to the total of Amount
for the Internet Channel. However, you can see that this approach has a limitation because you
cannot use the existing selection of attributes in your filter condition. In other words, you are
replacing the filter context for a table with a new one, but you are not able to change only part
of the filter context by using this process. When you want to remove only one selection of the
user (for example the Channel) while keeping all the other filters in the filter context, you need
to use the CALCULATE function. We more thoroughly describe the CALCULATE function later
because it is very powerful and flexible and deserves its own section; nevertheless, it is useful
to start taking a look at it now.
Let us imagine that you want to remove the filter on the Channel attribute from the filter
context but still want to keep all the other filters. Using CALCULATE, you can specify a filter on a
column that overrides any existing filter for that column only. You can define an AllChannels
measure with the following expression:
CALCULATE( SUMX( Orders, Orders[Amount] ), ALL( Orders[Channel] ) )
The first parameter of CALCULATE is the expression that you want to evaluate in a filter
context that is modified by the other parameters.
Note There could be any number of parameters in the CALCULATE function after the first
one, and each of these parameters defines a set of values for a column or a table that clears
the existing corresponding filter of the current filter context, replacing it with a new one.