dax NOT in

[adavid999]

Helllo, 

rather than writing out all this in example 1 (which gives correct result), how do I specify the NOT in (i want to count everything in table[sport] column except "09" and "99"

1. this gives right result

my measure = 
    CALCULATE(count(table[sport]), 
    table[sport] in {"01","02","03","04","05","06","07","08","10","11","12"},
    table[year] = "2017/18")

 do you know why these three examples below don't work?

2. 

test = 
    CALCULATE(count(table[sport]), 
    (table[sport]<> "09" &&
    table[sport]<> "99"),
    table[year] = "2017/18")

 3.

test2 = 
    CALCULATE(count(table[sport]), 
    not(table[sport] in {"09","99"}),
    table[year] = "2017/18")

 4.

test3 = 
    CALCULATE(count(table[sport]), 
    table[sport] <> "09",
    table[sport] <> "99",
    table[year] = "2017/18")

 Thanks

[Samarth_18]

Hi @adavid999 ,

Can you try this:-

test3 =
CALCULATE (
    COUNT ( table[sport] ),
    FILTER (
        table,
        ( table[sport] <> "09"
            || table[sport] <> "99" )
            && table[year] = "2017/18"
    )
)

Thanks,

Samarth

[adavid999]

@Samarth_18 I tried but this didn't give me the result I was expecting

[Samarth_18]

@adavid999 , Can you please share your PBIX after removing sensitive data and also the expected output?

[Greg_Deckler]

@adavid999 You mean like NOT("something" IN 'Table'[Column])

[adavid999]

@Greg_Deckler hi there, yes, in this case i want to count everything that is not "09" and not "99" in table[sport] where availabe values are: 

"01","02","03","04","05","06","07","08","09","10","11","12","99"

 Just lookinf for a better way of writing it as my example 1 above, does the job. Also, I'm not 100% clear on why my other examples don't work. 

[ryan_mayu]

@adavid999 

maybe you can try this

test3 = 
    CALCULATE(count(table[sport]), 
    (table[sport] <> "09"||
    table[sport] <> "99" &&
    table[year] = "2017/18")

[adavid999]

hi @ryan_mayu no this did not work. Your suggestion...

test3 = 
    CALCULATE(count(table[sport]), 
    (table[sport] <> "09"||
    table[sport] <> "99" &&
    table[year] = "2017/18")

 gives a different result to this (which gives the result I expect)

my measure = 
    CALCULATE(count(table[sport]), 
    table[sport] in {"01","02","03","04","05","06","07","08","10","11","12"},
    table[year] = "2017/18")

I want to count values that are not "09" or "99" from available values that are:

"01","02","03","04","05","06","07","08","09","10","11","12","99"

 grateful for any help with this.