ALLEXCEPT Without CALCULATE 09-23-2022 06:30 AM

[Greg_Deckler]

In my Don't Use CALCULATE! video, I demonstrated the many downsides to DAX's CALCULATE function. Namely, it is confusing and difficult to use for those new to DAX, it doesn't work very well in single table data models, is difficult to debug and; generally, is pretty much unnecessary in the vast majority of situations.

But! How then do you implement an ALLEXCEPT without using CALCULATE you may ask? Well, here you go. ALLEXCEPT Without CALCULATE. It's actually super simple.

Allexcept without ALLEXCEPT = 
    VAR __Months = DISTINCT('Table'[Month])
    VAR __Table = ALL('Table')
    VAR __Table1 = FILTER(__Table,[Month] IN __Months)
RETURN
    IF(
        ISFILTERED('Table'[Month]),
        SUMX(__Table1,[Value]),
        SUMX(__Table,[Value])
    )

eyJrIjoiMmE0ODk2ZDEtMmQwYy00MDQyLWI2OWQtYzVhNjE0NmI0ZmI5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9