Forum Discussion
Solved
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 i...
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] )
AlexisOlson
Super User
Super UserThe 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.