Forum Discussion
Dax: remove all filters except one
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.
Solution SageThis 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.
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.
That's it, thank your for your answer. 🙂