Optimizing DAX Calculations: A Journey through CALCULATE and Filter Context

[technolog]

Let's see how the internal context of the CALCULATE filter can be used to remove previously imposed function filters with respect to the CALCULATE-wrapped expression in question, or with respect to an external custom context filter.

Our source table

 

In front of us we have a table with sales for each product, where we have Self-Cost and Total. Let's output the result in the form of a table using the SUMX function

 

Let's understand how the Profit value was calculated. Before it was calculated, DAX evaluated all filters. In the visualization itself, each row is a filter, i.e. the Product ID column filter is applied to calculate this cell.

Next, DAX went down inside the formula, met SUMX and begins to calculate the first parameter SUMX, ie the table Orders. But our external context filter by ProductID=10001 is applied to the Orders table. As a result, the table is filtered by ProductID, and then SUMX calculates the profit expression clearly according to the filtered table.

Let's add a column where we get the corresponding Profit value expressed as a percentage. To do this, we need to divide the private profit (for a specific product) by the profit for all products. But if we consider the calculation of a particular cell, this particular cell will be calculated within a row, and within a row we have a ProductID filter, so this cell will also be subject to the filter.

Let's create a new measure, let's call it 'ProfitMeasure%'. In order to calculate this expression, we need to clear the filters from the table beforehand. We wrap the code in the ALL function and get the total profit amount for all products.

 

Adapt the calculation of the measure to the calculation of percentage values

 

If we add filters, we can see that the calculation of percentages is not working correctly

 

 

In order for other external filters to work with this table, ALL will need to be wrapped in SUMX and SUMX will need to be wrapped in CALCULATE.

As we can see, adding the ALL function applied to the whole table does not solve our problem. Let's try to specify a column as a parameter in ALL

 

Now we have considered the ALL function, in the parameters of which we have specified a table and a column. But besides the ALL function, we have a number of other equally useful functions.

---

For example, the ALLEXCEPT function, which clears filters from the entire table, except for the columns specified in the parameters (this is the reverse of ALL([Column1]).

ALLEXCEPT - clears the table from filters, except for the columns specified in the parameters.

The ALLEXCEPT function contains two parameters. As the first one we pass the table from which we want to remove all filters. And as the second and subsequent parameters we specify the columns from which we do not want to remove filters.

If we specify the Orders table as a parameter and Email as a column, then the filters will be removed from the remaining columns. The function will return us a table consisting of all the columns other than the specified ones. The Email column will no longer be there, so the function will not remove the filters.

Let's see how the function works in practice. Replace the ALL function with ALLEXCEPT in our code

 

Another problem arises - if we want to calculate % profit not for all Product IDs, but for a certain number, the formula breaks down again.

 

The problem is that on the one hand we want to enter a filter by ProductID, but on the other hand - when there is a slice for each product, in order to calculate it, it is necessary to remove the filter by product. We need to make it so that the selected filters for calculation remain, but ProductID is not shown in the visualization

---

To do this, let's try to apply the ALLSELECTED function. If we apply this function, the last applied filter is removed from a particular table/column.

There are levels of filters, meaning we can apply the most important filter that applies to the entire file including all pages - this is the report level filter. This is followed by a page filter assigned to a specific page.

Then we can have several visualizations on one page, which can filter each other. There is another level in the summary table visualization itself, which calculates the measure. Within the external filter context, this is the most recent filter level and it is the one that ALLSELECTED removes

 

Now our measure works fine, despite the filter entered on the Email column

 

Now let's add another filter on the Status column and compare the results for different measures

 

As we can see, taking into account our formulas in the current situation, only the ALLSELECTED variant is considered correctly.