Microsoft Office Tutorials and References
In Depth Information
Adding Filters to a Filter Context for a Single Table
The filter context can be modified to evaluate a particular DAX expression, to get a value for
a different context. For instance, you might want to sum a value over a subset of the rows of
a table, so you need a way to define the rows you want to include in that calculation.
All of the aggX aggregation functions have syntax with two parameters: the first one is
the table that will be used to iterate rows (filtered by the current filter context), the second
parameter is the expression applied to each of the iterated rows.
SUMX( <table>, <expression> )
Instead of a table, in the first parameter, you can use a function returning a table. For example,
you can use the FILTER function, which gets a table and further restricts the filter context on
that table by using the Boolean expression received as the second parameter.
FILTER( <table>, <filter expression> )
Simply said, the expression passed to a FILTER function adds that filter to the current filter
context for that table. As we said, you use FILTER in place of a table in a DAX expression. For
example, if you write the following:
SUMX( FILTER( Orders, Orders[Quantity] > 0 ), Orders[Amount] )
SUMX( Orders, Orders[Amount] )
you get the same result because in our sample data there are no rows with a Quantity value
that is less than zero. However, you might consider only rows with a price greater than 1:
SUMX( FILTER( Orders, Orders[Price] > 1 ), Orders[Amount] )
In this case, the FILTER function skips just the <Internet, Green, Small> row from the table we
saw in Figure 6-9 (which has a price of 1 and is excluded by the filter condition). Using that
formula for your CalcAmount measure, you ignore that row in the PivotTable calculation.
As you can see in Figure 6-10, there is no value for the highlighted F5 cell in the PivotTable,
and the total for rows and columns ignores the filtered row, too. So the FILTER function can
filter data by restricting the filter context used (for example) to calculate aggregations in a