Forum Discussion

Dillan's avatar
Dillan
Frequent Visitor
7 years ago
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...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    7 years ago

    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]
)
Dillan's avatar
Dillan
Frequent Visitor
7 years ago

I have other tables in my report, but in this visualization, it all pulls from the same, single table. 

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
7 years ago

That's pretty odd-looking behavior. I think you'd need to share the PBIX for anyone to tell what's going on.