DAX Power BI: Calculating revenues by the manager — FILTER or CALCULATE?
Let’s consider in practice the basic differences between the FILTER and CALCULATE functions.
Previous articles on this topic:
Consider the initial table
And let’s add two measures to it
Both measures calculate profit based on SUMX and this amount is filtered by the Manager field.
The FILTER measure calculates profit based on “Manager 1”. Filtering takes place in the very first parameter of SUMX, and further on the filtered table SUMX calculates Total and summarizes.
If we display these two measures on the page, we will see the same result for both measures
In one case we first filtered the whole table and then summarized Total for it.
In the other case, we took the filter from the CALCULATE function, applied it to the expression itself and before calculating it, we filtered the table by “Manager 1” and then SUMX calculated the sum by Total for the filtered table.
On the surface there is no difference. But if we place these two measures in the table (the table already contains managers by rows in advance), we will see that the result is different.
For manager “Manager 1”, it converges, but the rest of them show empty values. On the one hand, everything is correct, because we calculated the profit for only one manager. But why did we get empty values from the DAX point of view?
The thing is that before this cell is calculated, the entire external context of the filter is evaluated. In the table itself, there is a filter on the row, and there is a filter on the column manager = “Manager 1”.
Next, DAX goes down inside this cell into the code itself, and there is a SUMX function in which the first parameter is evaluated first.
The FILTER function also calculates the first parameter first, and there it is, the Orders source table. But before the source table is returned, it is filtered by an external context filter.
For example, let in this case, let it be manager = “Manager 2”. The table filtered by manager is fed into the FILTER function. And then the function tries to impose its new filter on the table for this manager that was compressed by the filter earlier. But this already compressed table contains only the row for manager “Manager 2”, and we are trying to compress it by manager = “Manager 1”. And since we do not have “Manager 1” in the compressed table, we get an empty table for output.
Let’s summarize how FILTER works: it imposes its filter on the previously filtered table, i.e. it tries to impose a filter on the remaining rows after the filtering, and then tries to return us either the table filtered by the specified filters, or return an empty table filtered by the specified filters.
But the situation with CALCULATE is quite different. The function simply deletes all filters and then applies its own filters, i.e. the replacement process takes place if the same columns are specified as filters.
Let’s assume that in CALCULATE there is a filter on the manager column = “Manager 2” and an external filter on the manager column as well. Let’s see how this cell is calculated.
The external context filter is manager “Manager 2”.
Here in the internal context, manager is equal to “Manager 1”.
So in the row where the external context filter says “Manager 2”, the calculation is done by “Manager 1”. And similarly for the rest of the managers.
When FILTER starts filtering cells, it pre-clears them and then adds its filter. This approach is what makes FILTER different from CALCULATE.
Don’t forget to subscribe to
and join our Power BI community
