Calculate - all except

DAX DAX Calculations
1

I have about twenty accounts that start with the numbers 40 Example is 400000, 400005, 400009, 400012…

I want to write a Calculate statement something like this.

Total Sales Modified = Calculate([Total Sales]), LEFT((Account),2) = “40”, NOT 400017, NOT 400020)

Basically, I want all the accounts that start with 40 except for a couple of them. So I want to grab all of them in one filter, then exclude just a couple of them.

I want all account

2

Hi @Usates,

Try something like this:

Total Sales Modified =
VAR SelectAcc =
    FILTER(
        ALL( Accounts[Account] ),
        LEFT( Accounts[Account], 2 ) = "40"
    )
VAR ExclAcc = { 400017, 400020 }
VAR FilterAcc = EXCEPT( SelectAcc, ExclAcc )
RETURN

    CALCULATE(
        [Total Sales],
        Accounts[Account] IN FilterAcc
    )
1 Like
3

Thank you. Works perfectly.