Forum Discussion

adavid999's avatar
adavid999
Helper V
4 years ago

dax NOT in

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

 

Need Help

7 Replies

    • adavid999's avatar
      adavid999
      Helper V
      4 years ago

      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's avatar
        ryan_mayu
        Super User
        4 years ago

        adavid999 

        maybe you can try this

        test3 = 
    CALCULATE(count(table[sport]), 
    (table[sport] <> "09"||
    table[sport] <> "99" &&
    table[year] = "2017/18")
  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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