Forum Discussion
ALLEXCEPT Still being impacted by Slicer
Hello,
I am using an ALLEXCEPT function to get total sales, still using a couple of report level filters I have in place. When I create it, I run into an issue where, when I select a slicer which is crucial to my report, but not one of the report level filters, the allexcept value will change. It only happens when I deselect "DNQ" from the "Minority Indic" Column. But when I choose any other option in that slicer, it doesn't change. I put in 3 images, will all filters selected, when I deselect DNQ, and when I deselect other filters. I don't understand why it filters only when I do this selection; otherwise it retains the qualities of the ALLEXCEPT formula.
Here is my ALLEXCEPT formula. The column in my slicer is "Minority Indic", which is not in this formula at all.
ALLEXCEPT = CALCULATE( sum('Final DBE Spend 2018'[ Invoice Value]), ALLEXCEPT('Final DBE Spend 2018', 'Final DBE Spend 2018'[Cty], 'Final DBE Spend 2018'[Pstng Date], 'Final DBE Spend 2018'[Vendor # Length]))
I put in the ALL formula so you could see this doesn't impact the ALL formula, just the ALLEXCEPT. Any explanation/solution to this problem would be great. Thanks!
Before DNQ DESelectedAfter DNQ DESelectedAfter deselecting other options
The issue is that DNQ is related to [Vendor # Length] in a non-obvious way.
If you remove DNQ, then all values of [Vendor # Length] are 7 or blank. Since [Vendor # Length] is part of the filter context you are preserving with ALLEXCEPT, when you remove DNQ from your selection, you also remove all 56,832 DNQ rows that have [Vendor # Length] = 4 from your sum. Basically, preserving filter context on [Vendor # Length] indirectly preserves filter context on [Minority indic.], which is not what you want.Try this instead:ALLEXCEPT = CALCULATE ( SUM ( 'Final DBE Spend 2018'[ Invoice Value] ), ALLEXCEPT ( 'Final DBE Spend 2018', 'Final DBE Spend 2018'[Cty], 'Final DBE Spend 2018'[Pstng Date] ), 'Final DBE Spend 2018'[Vendor # Length] )
6 Replies
- AlexisOlson
Super User
Are there any other tables involved? If so, what does your relationship diagram look like?
I have other tables in my report, but in this visualization, it all pulls from the same, single table.
- AlexisOlson
That's pretty odd-looking behavior. I think you'd need to share the PBIX for anyone to tell what's going on.
- AlexisOlson
The issue is that DNQ is related to [Vendor # Length] in a non-obvious way.
If you remove DNQ, then all values of [Vendor # Length] are 7 or blank. Since [Vendor # Length] is part of the filter context you are preserving with ALLEXCEPT, when you remove DNQ from your selection, you also remove all 56,832 DNQ rows that have [Vendor # Length] = 4 from your sum. Basically, preserving filter context on [Vendor # Length] indirectly preserves filter context on [Minority indic.], which is not what you want.Try this instead:ALLEXCEPT = CALCULATE ( SUM ( 'Final DBE Spend 2018'[ Invoice Value] ), ALLEXCEPT ( 'Final DBE Spend 2018', 'Final DBE Spend 2018'[Cty], 'Final DBE Spend 2018'[Pstng Date] ), 'Final DBE Spend 2018'[Vendor # Length] )It worked! Thanks Alexis, you are awesome!!! Is there some way to reward people in this forum? Like points or likes or something?
- AlexisOlson
You can accept a solution as well as give kudos with the thumbs up.
