DAX Power BI: Using FILTER and CALCULATETABLE

The basic difference between these functions is how they perform filtering. FILTER always calculates the first parameter first, and then the filter is evaluated.

CALCULATE and CALCULATETABLE, on the other hand, first apply all the filters and then evaluate the expression. When all filters are evaluated, they are merged with an external context filter and this merging is possible within the framework of replacement, deletion, addition of previous filters. Within this merged context, the final expression is evaluated.

Let’s consider the initial table

On the basis of the initial table we will build a summary table by managers and work with this table

Let’s add an auxiliary measure that we will use for filtering and validate this measure against managers.

Let’s filter this table using several different filters.

Let’s display only those managers whose Total is 9/10 of the maximum value of Total

We start with CALCULATETABLE, but it turns out that we can’t specify measures in filter conditions.

We change CALCULATETABLE to FILTER and the table is filtered. It turns out that we can use measures in FILTER.

In CALCULATE() and CALCULATETABLE(), we cannot use measures, but we can replace our measure with a MAX function and get the same result

Let’s consider filtering of items for the obtained tables. To do this, let’s set up a simple information model.

And set up the following visualization

We see that in this visualization the return values of these functions coincide

What are the features of the differences we will consider in the continuation of this article.