Why is ALLEXCEPT not working as intended?

[tyw214]

So first of all, I have a table which is a crossjoin:

TBL_ClientMonth_Corssjoin = 

CROSSJOIN(SUMMARIZE('TBL_Client_Prof_Dates','TBL_Client_Prof_Dates'[YearMonth]),SUMMARIZE(FILTER('CustProf Order',NOT(ISBLANK('CustProf Order'[AccountId]))),'CustProf Order'[AccountId]))

i then linked the YearMonth of the CrossJoin back to the dates of the date table as shown above.

Now I tried a simple measure that should ignore all slicers except date slicers Client_Prof_Dates[Date]:

All Except Dates = Calculate(SUM('TBL_ClientMonth_Corssjoin'[Monthly Revenue]), ALLEXCEPT(Client_Prof_Dates,Client_Prof_Dates[Date]))

This measure returns a number that is effected by every slicer including a Client Name slicer which is derived from the TBL_ClientMonth_Corssjoin.

However, if I do:

All Except Dates = Calculate(SUM('TBL_ClientMonth_Corssjoin'[Monthly Revenue]), ALL(TBL_ClientMonth_Corssjoin[Client Name])

It properly ignroes the Client name table, and will be filtered correctly ONLY by the date slicers Client_Prof_Dates[Date] and not the TBL_ClientMonth_Corssjoin[Client Name] slicer.

Why is that?

[v-stephen-msft]

Hi @tyw214 ,

Please try this

All Except Dates =
CALCULATE (
    SUM ( 'TBL_ClientMonth_Corssjoin'[Monthly Revenue] ),
    FILTER (
        ALLSELECTED ( Client_Prof_Dates ),
        [Date] = MAX ( Client_Prof_Dates[Date] )
    )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tyw214]

Tried this formula, and this return the sum of only the Selected Client Name 😞