cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmace1 Member
Member

DAX equivalent of SQL's NOT IN?

There's an extremely similar equivalent for SQL's IN - I have no imagination, so an example extremely similar to the last one I actually wrote: 

 

Card Sales =
CALCULATE (
    [total valid spend],
    FILTER ( payments, paymentypeid = 7 && paymentprocessorID IN { 1, 5, 6, 9 } )
)

NOT IN doesn't work - what's the simpliest DAX equivalent of SQL's NOT IN? 

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: DAX equivalent of SQL's NOT IN?

Hi @mmace1,

 

You need to place the NOT formula on the column and then use the in to make the level you need.

 

So you measure should look something like this:

MEASURE NOT IN =
CALCULATE (
    SUM ( Table1[Value] );
    FILTER (
        Table1;
        Table1[Category] = "C"
            && NOT ( Table1[CAT LEVEL] ) IN { 2; 3 }
    )
)

As you can see below in category C the total is 1500 if you took out the category 2 and 3 it's 1000.

 

notin.png

 

Regarrds,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Highlighted
Super User
Super User

Re: DAX equivalent of SQL's NOT IN?

Hi @mmace1,

 

You need to place the NOT formula on the column and then use the in to make the level you need.

 

So you measure should look something like this:

MEASURE NOT IN =
CALCULATE (
    SUM ( Table1[Value] );
    FILTER (
        Table1;
        Table1[Category] = "C"
            && NOT ( Table1[CAT LEVEL] ) IN { 2; 3 }
    )
)

As you can see below in category C the total is 1500 if you took out the category 2 and 3 it's 1000.

 

notin.png

 

Regarrds,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mmace1 Member
Member

Re: DAX equivalent of SQL's NOT IN?

Typo on your measure name (it's not 2 & 3, but the Measure title says not 1 & 2). 

 

Exactly the syntax I was looking for, thanks!

Super User
Super User

Re: DAX equivalent of SQL's NOT IN?

Hi @mmace1,

 

Nice catch on the typo. 

 

But other than that believe it works like you want correct?

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mmace1 Member
Member

Re: DAX equivalent of SQL's NOT IN?

Yes, exactly - thanks!