Forum Discussion
Dax: remove all filters except one
Hi,
I am interested in creating a DAX measure that displays a dynamic value, changing according to its visual filter. Would it be possible to make this measure impervious to all the other filters (including the slicers selected by the user) ?
Let's take an example of sample data:
I have a short table showing a measure result "CANADA" because its visual filter is on Canada.
However, when I select a slicer value (in the screenshot, a value from the sales field), the measure can go blank if the country is not related to the slicer.
How to avoid the measure from going blank, and more precisely how to clear all filters (including the sales slicer) except the visual filter that allows us to show the country we want ?
- The first solution could be to remove interactions from the slicer visual and the table visuals. However, this couldn't be a definitive solution if we want maintainability (the more slicers and pages we have, the more interactions we will have to edit).
- A track I found would be to use the calculate and filter functions. As I want to remove all filters but the visual one, I thought this DAX expression would be the solution:
CALCULATE(
UPPER(MIN(financials[Country])),
ALLEXCEPT(financials, financials[Country])
)Unfortunately, this code is not solving the problem. I still have blank values. Why?
Here is the dashboard sample I used: https://drive.google.com/file/d/1caFLlg3Y5_3yhyoSd6aRg4HNXghhiWvn/view?usp=sharing . Thanks !
Either of these solutions will work:
This time, the issue is because you have a many-to-one relationship between BU and Employee, but the Cross-filter direction selected is 'Both'.Unless there is a very good reason, you should have only 'Single' directionality between your dimension tables and your fact tables. This is best practice.
If you do have a very good reason, then I would suggest modifying the interactions between visuals.
Select the slicer you want to not affect the visuals, then go to Format -> Edit Interactions, and turn off interactions between the slicers and the visuals that you want to ignore it.
8 Replies
kpostSolution Sage
This is happening because the data is all coming from a single table. This is a common issue that 'breaks' the expected behavior of ALL, ALLEXCEPT etc.
You need to go to the data model and select "Independent" for the Value Filter Behavior of this table (See below image).
Also, as a minor point, I would modify the DAX to this. It's not that MIN doesn't work, it's just that using MIN in this case might be confusing to the person maintaining the report. SELECTEDVALUE() makes more sense.
Test =CALCULATE(UPPER(SELECTEDVALUE(financials[Country])),ALLEXCEPT(financials, financials[Country]))I have attached the .pbix report. Please mark this reply as the solution if it works for you! Thank you.
Here is a video explaining this issue:
https://www.youtube.com/watch?v=gTZ8YKW425s
And here is an article on Value Filter Behavior, if you want to do a deep dive into the mechanics.
https://learn.microsoft.com/en-us/power-bi/transform-model/value-filter-behavior///Mediocre Power BI advice, but it's free///
Hello, thank you for your time and your answer.
This is kind of my bad this time because I used one table in the sample data (which is not the case for my corporate data).
- When I use only one table (like the first sample above), everything's working well when I change the filter behaviour.
- However, when I use numerous tables in a star schema, it does not seem to work. Does the calculate behaviour change depending on the cross-filter direction?
I just kept the same formula but in this new multi-table sample.
CALCULATE( UPPER(SELECTEDVALUE(BU[Region])), ALLEXCEPT(BU, BU[Region]) )Also, thank you for the value filter behaviour, I was not aware of a such thing 😉 .
Here is the star sample link: https://drive.google.com/file/d/1Kt2MaAvn6P8Bx5I4YXZbT09IhlZEYjiv/view?usp=sharing . For convenience purposes, I used this new sample.
- kpost
Either of these solutions will work:
This time, the issue is because you have a many-to-one relationship between BU and Employee, but the Cross-filter direction selected is 'Both'.Unless there is a very good reason, you should have only 'Single' directionality between your dimension tables and your fact tables. This is best practice.
If you do have a very good reason, then I would suggest modifying the interactions between visuals.
Select the slicer you want to not affect the visuals, then go to Format -> Edit Interactions, and turn off interactions between the slicers and the visuals that you want to ignore it.
- FBergamaschi
Super User
Your code removes all filters from the financials table, but not the one on the country column, if it is there
You probably have other filters at work that are giving you blank
Try
CALCULATE( UPPER(MIN(financials[Country])), ALLEXCEPT(financials, financials[Country]),
REMOVEFILTERS () )If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
- kpost
This will not work. It removes the 'country' visual-level filter and then MIN() causes the measure to always result in "CANADA", or whatever country comes first alphabetically. See image.
- wardy912
Hi TheoAu
Your measure just needs to be a bit more specific, try this:
CALCULATE( UPPER(MIN(financials[Country])), REMOVEFILTERS(ALL(financials)), KEEPFILTERS(financials[Country]) )REMOVEFILTERS(ALL(financials)): Clears all filters on the financials table, including those coming from slicers or relationships.
KEEPFILTERS(financials[Country]): Re-applies only the filter from the visual context.
I hope this helps, please give a thumbs up and mark as solved if it does, thanks!
