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
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!




View solution in original post

4 REPLIES 4
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!




View solution in original post

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!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 65 members 1,164 guests
Please welcome our newest community members: