dax NOT in

Question

Helllo,&nbsp;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"&nbsp;1. this gives right resultmy measure = 
    CALCULATE(count(table[sport]), 
    table[sport] in {"01","02","03","04","05","06","07","08","10","11","12"},
    table[year] = "2017/18")&nbsp;do you know why these three examples below don't work?2.&nbsp;test = 
    CALCULATE(count(table[sport]), 
    (table[sport]&lt;&gt; "09" &amp;&amp;
    table[sport]&lt;&gt; "99"),
    table[year] = "2017/18")&nbsp;3.test2 = 
    CALCULATE(count(table[sport]), 
    not(table[sport] in {"09","99"}),
    table[year] = "2017/18")&nbsp;4.test3 = 
    CALCULATE(count(table[sport]), 
    table[sport] &lt;&gt; "09",
    table[sport] &lt;&gt; "99",
    table[year] = "2017/18")&nbsp;Thanks&nbsp;

greg_deckler · Answer

adavid999&nbsp;You mean like NOT("something" IN 'Table'[Column])

adavid999 · Answer

Greg_Deckler&nbsp;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:&nbsp;&nbsp;"01","02","03","04","05","06","07","08","09","10","11","12","99"&nbsp;&nbsp;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.&nbsp;

ryan_mayu · Answer

adavid999&nbsp;
maybe you can try this
test3 = 
    CALCULATE(count(table[sport]), 
    (table[sport] &lt;&gt; "09"||
    table[sport] &lt;&gt; "99" &amp;&amp;
    table[year] = "2017/18")

adavid999 · Answer

hi&nbsp;ryan_mayu&nbsp;no this did not work. Your suggestion...test3 = 
    CALCULATE(count(table[sport]), 
    (table[sport] &lt;&gt; "09"||
    table[sport] &lt;&gt; "99" &amp;&amp;
    table[year] = "2017/18")&nbsp;gives a different result to this (which gives the result I expect)&nbsp;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"&nbsp;grateful for any help with this.

samarth_18 · Answer

Hi&nbsp;adavid999&nbsp;,
&nbsp;
Can you try this:-
test3 =
CALCULATE (
    COUNT ( table[sport] ),
    FILTER (
        table,
        ( table[sport] &lt;&gt; "09"
            || table[sport] &lt;&gt; "99" )
            &amp;&amp; table[year] = "2017/18"
    )
)
&nbsp;
Thanks,
Samarth

Forum Discussion

7 Replies

Related content