Why CALCULATE is not modifying filter context when used with FILTER?

There are some tricky details on CALCULATE that produce different results. I’ll show the differences and provide examples below.

Measure One

One = CALCULATE(SUM(Years[Sales Amount]),FILTER(Years, 'Years'[Year] = 2010))

First, remember that every language calculates from the most internal to the most external function.

It means that Power BI will first calculate the function FILTER, which will return a table that contains only the year 2010. Only after that Power BI will perform CALCULATE.

A tricky part here: If there is a filter in the 2nd parameter, the CALCULATE can ignore the slicer (more details on measure two). However, there is no filter on the 2nd parameter, there is only a table! (previously calculated, which contains only year 2010 now).

Since there is no filter, CALCULATE applies the context filter (the slicer in your example) excluding anything before 2013. Given that, no rows match the filters for Measure One and the result will be blank (if the slicer is set to 2013-2018, of course).

Measure Two

Two = CALCULATE(SUM(Years[Sales Amount]),'Years'[Year] = 2010)

Measure Two do have a filter expression: 'Years'[Year] = 2010

When filter expressions are provided, the CALCULATE function changes the filter context (which contains slicers or filter on page etc.) to evaluate the expression.

For each filter expression, there are two potential outcomes in CALCULATE:

Outcome A. If the columns are already in the filter context, the existing filters will be overwritten by the parameter in the CALCULATE expression. I.e., does not matter the slicer (or filter on this page or on all pages…)

That is the case for your measure two!

Since you are using 'Years'[Year] in both the CALCULATE expression and the slicer, the context filter will be overwritten by the filter in the CALCULATE expression. I mean, the result is ignoring the slicer/filter context and you will find "3" as the result.

Outcome B. If the columns aren't in the filter context, then new filters will be added to the filter context to evaluate the expression. In other words, it will consider both the slicer/filter context and the filter expression in CALCULATE.

To exemplify this behavior, I have created a new measure:

ThreeSalesFilter = CALCULATE(SUM(Years[Sales Amount]), 'Years'[Sales Amount]=3)

It keeps the exact same row as measure two, right? The Year=2010 is the same row Sales Amount=3. However, the result of this measure is blank and not 3. Quite interesting!

Again, it happens because CALCULATE is using 'Years'[Sales Amount] but the slicer/filter context is using 'Years'[Year] – another column. Therefore the CALCULATE filter "joins" the filter context; do not overwrite it.

Below, the reproduction of the dashboard including my third variable.

If you need more details or clarifications, please let me know in the comments and I'll edit here.

You are getting different behaviors because of the way that the CALCULATE function works with filter context; you are passing two different types of arguments into the calculate function.

• In measure one you are passing a table filter expression. It takes the table created by the FILTER function, and then applies the filter context given by the slicer. Table filter expressions do not override the existing filters on any columns.

• In measure two you are passing a Boolean filter expression as the second argument. It overrides the filter context since they both filter based on the year column, therefore ignoring the slicer.

MS best practices do caution against using FILTER as a filter argument, but they do also mention examples of several times where it is appropriate.

• I try to create a table like yours

• also create the two measures which build on the dashboard

So, I think the values displayed by One and TWO are not the same as the DAX syntax. But your One is BLANK that the possible reason is that One caught the filter condition on the right side of your way, while TWO caught the value of the table on the left, I guess it is because it's not the same.