Forum Discussion
REMOVEFILTERS Function Not Working and Filter still applying
Hello, I used the below formula to Calculate Market Lines but with the condition that BOD Filter doesn't apply to the calculation of expression- Market Lines. However, the filter BOD was still applying and not getting removed even with the removefilters function
But, if I change the measure to below, the BOD filter(slicer)is not applying in the calculation of the expression-Market Lines. Is it because using 'Main Import' vs ALL('Main Import'[IN MARKET INDICATOR]) was nullifying the Remove Filters function and enabling the Main Import to have any filter applied?
Hi Bambroo69
When the expression FILTER ( 'Main Import', ... ) is included as a filter argument within CALCULATE, it acts as a filter including the combinations of values from all columns of 'Main Import' (actually the expanded table) evaluated in the original filter context. This would include the column 'Main Import'[BOD].
This means this filter argument includes the visible values of 'Main Import'[BOD] from the original filter context, which would mean REMOVEFILTERS ( 'Main Import'[BOD] ) ends up having no effect in this case.
I would recommend rewriting the first measure like this:
MARKET LINES (REMOVE FILTERS) = CALCULATE ( [Market Lines], KEEPFILTERS ( 'Main Import'[IN MARKET INDICATOR] = "Y" ), REMOVEFILTERS ( 'Main Import'[BOD] ) )KEEPFILTERS ensures that that particular filter is intersected with existing filters. If you want to override existing filters then remove KEEPFILTERS.
Does the above measure give you the expected result?
As a general rule, I would recommend applying filters to specific columns rather than entire tables in CALCULATE.
Here are a couple of useful articles:
https://www.sqlbi.com/articles/expanded-tables-in-dax/
https://www.sqlbi.com/articles/using-keepfilters-in-dax/
Regards
3 Replies
- OwenAuger
Super User
Hi Bambroo69
When the expression FILTER ( 'Main Import', ... ) is included as a filter argument within CALCULATE, it acts as a filter including the combinations of values from all columns of 'Main Import' (actually the expanded table) evaluated in the original filter context. This would include the column 'Main Import'[BOD].
This means this filter argument includes the visible values of 'Main Import'[BOD] from the original filter context, which would mean REMOVEFILTERS ( 'Main Import'[BOD] ) ends up having no effect in this case.
I would recommend rewriting the first measure like this:
MARKET LINES (REMOVE FILTERS) = CALCULATE ( [Market Lines], KEEPFILTERS ( 'Main Import'[IN MARKET INDICATOR] = "Y" ), REMOVEFILTERS ( 'Main Import'[BOD] ) )KEEPFILTERS ensures that that particular filter is intersected with existing filters. If you want to override existing filters then remove KEEPFILTERS.
Does the above measure give you the expected result?
As a general rule, I would recommend applying filters to specific columns rather than entire tables in CALCULATE.
Here are a couple of useful articles:
https://www.sqlbi.com/articles/expanded-tables-in-dax/
https://www.sqlbi.com/articles/using-keepfilters-in-dax/
Regards
It works. One last question, I understand Modifers in the Calculate are evaulated first versus other filters. If there are multiple modifiers as in the Measure you have provided above, are they calculated based on their sequence in the measure?
- OwenAuger
Bambroo69 Glad to hear it 🙂
Modifiers are indeed evaluated before "explicit filter arguments" (using terminology from The Definitive Guide to DAX).
However, the order arguments are listed in CALCULATE doesn't make any difference.
There must be various rules governing how different modifiers interact, though I couldn't point to any documentation on that. For example, I would assume that ALLSELECTED would behave as though it's applied "after" ALL, though I haven't specifically tested that recently.
The exact way modifiers interact is an interesting topic to explore though!
