Filtering with table functions
One of the most profound insights when working with DAX is the deep connection between tables and filtering. In this section, you will learn what this connection is, and how to leverage it.
Using CALCULATETABLE
As we discussed earlier in this chapter, table expressions used in table aggregation functions like SUMX are evaluated in the same context as the table aggregation function itself. This is not always what you want: sometimes, you need a different context. DAX provides a function for precisely this purpose: CALCULATETABLE.
Like its cousin CALCULATE, CALCULATETABLE changes context before evaluating an expression. In CALCULATE, this expression must return a scalar value; in CALCULATETABLE, it must be a table expression. Apart from that, the function works with the same four-step process:
- Set up a filter context.
- Remove existing filters from columns or tables referred to in the filter arguments.
- Add new filters as...
