Calculate with and without a filter table argument & diff. results

DAX DAX Calculations
2

Hello @AussieDragon,

Thank You for posting your query onto the Forum.

Please always try to post your reference working or demo PBIX file so that members of the Forum can provide assistance in a better and efficient manner. For poster of the query, it may be a general question but the members who are providing the assistance, for them it’s not. They might have to build a file from scratch in order to create the environment which unnecessarily consumes more of their time who are trying to provide assistance.

Now, here’s answer to your question in a two-fold steps (Note:- In my PBIX file, I don’t have a field for “Color” so I’ve used a “Brand Name” to explain the scenario) -

1). Without FILTER -

When you write a expression like this i.e., Without explicitly using the FILTER function -

Contoso Sales Without Filter = 
CALCULATE( [Total Sales] , 
    'Product'[BrandName] = "Contoso" )

At the back-end of engine, Power BI automatically evaluates an expression to this -

Contoso Sales Without Filter = 
CALCULATE( [Total Sales] , 
    FILTER( ALL( 'Product'[BrandName] ) , 
        'Product'[BrandName] = "Contoso" ) )

It means that, filter context over “Contoso” over-writes all the other filters which are there inside the visual as well as outside the visual. Because the expression states that, out of all the “Brand Names” evaluate the results for “Contoso” and then “ALL()” function over-writes all the other filters thereby, repeating the same results for “ALL” the other brands as well. So, even if you make a slicer selection over any other “Brand Name”, it’ll still showcase the results for “Brand Name = Contoso”. Below is the screenshot provided for the reference -

Contoso Sales Without Using FILTER Function - 1
Contoso Sales Without Using FILTER Function - 11082×687 24 KB

Contoso Sales Without Using FILTER Function - 2
Contoso Sales Without Using FILTER Function - 21058×525 16.9 KB

2). With FILTER -

Now, when a expression is written using FILTER function. Like this -

Contoso Sales With Filter = 
CALCULATE( [Total Sales] , 
    FILTER( 'Product' , 
        'Product'[BrandName] = "Contoso" ) )

At the back-end of the engine, Power BI scans the entire “Product” table and and keeps the rows only where “Brand Name = Contoso” and rest of the rows are eliminated. So it means that, if “Brand Name = Contoso” then evaluate the results or else it’ll showcase it as blanks.

It’s not a recommended best practice as per DAX to scan the entire “Product” table. Because when an entire table is scanned, it puts more pressure on the engine and slows down the evaluation. In order to achieve that same result, the expression can be written like this -

Contoso Sales With Filter = 
CALCULATE( [Total Sales] , 
    KEEPFILTERS( 'Product'[BrandName] = "Contoso" ) )

The same can be written in a convoluted manner which yields the same results -

Contoso Sales With Filter = 
CALCULATE( [Total Sales] , 
    FILTER( ALL( 'Product'[BrandName] ) , 
        'Product'[BrandName] = "Contoso" ) , 
    VALUES( 'Product'[BrandName] ) )

In this case, we are stating that, only retain the filter over “Brand Name = Contoso” and don’t over-write the other filter contexts, thereby respecting the other filter contexts as well for each Brand Names. But by writing an expression in the form of “KEEPFILTERS()”, we optimize the measure as well whereas in the case of original expression which you’ve written, it has to scan the entire “Products” table and then evaluate the results.

I’m also attaching the working of the PBIX file alongwith the link of a post for your reference purposes where almost similar query was addressed few days back onto the Forum.

EDIT:- Below are additional links of the articles and blog posts provided from SQLBI website with regards to your query about how filter context works.

Hoping you find this useful and helpful.

Thanks and Warm Regards,
Harsh

With and Without FILTER function.pbix (49.6 MB)

4 Likes