Allexcept not working

[melanie-g]

Hi there,

I have 2 models where my ALLEXCEPT calculation works in one but not the other. 

Screen shots below of the model that does not work. 

See below my calculation....

For your reference:

eeCO_Desc - renamed to Company

eeBR_Desc - renamed to Dealership Location

eeDP_Desc - renamed to Deparment

eeFR_Desc - renamed to Franchise

I want this to filter down to the Franchise level which it doesn't appear to do.

Please refer to the yellow highlights...

The "New" line shows MTD units of 29,697, however the result from the "Check" formula is 1320659 (where I want it to show 29,697)

Similarly, the "Subaru" line shows MTD units of 9,169 and the "Check" formula produces 283654 (where I want it to show 9,169) etc...

I have used the same logic from my other model that works correctly, but for some reason it doesn't work here.

Any help would be appreciated.

Thank you.

[v-yalanwu-msft · Accepted solution]

Hi, @melanie-g ;

You can replace Allexcept() with Filter(allselected('table'),[group]=max('table'[group]))

Check =
CALCULATE (
    SUM ( [MTD] ),
    FILTER (
        ALLSELECTED ( 'table' ),
        [eeCO_Desc] = MAX ( [eeCO_Desc] )
            && [eeBR_Desc] = MAX ( [eeBR_Desc] )
            && [eeDP_Desc] = MAX ( [eeDP_Desc] )
            && [eeFR_Desc] = MAX ( [eeFR_Desc] )
    )
)

 
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@melanie-g,

Try rewriting your measure to use ALL/VALUES instead of ALLEXCEPT. The article below explains the differences between these two approaches. If this doesn't solve it, please provide a sample pbix using one of the file services like OneDrive.

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/