cancel
Showing results for
Did you mean:
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

## 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.

Regarrds,

MFelix

Proud to be a Datanaut!

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

Regarrds,

MFelix

Proud to be a Datanaut!

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

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

Proud to be a Datanaut!

Member

## Re: DAX equivalent of SQL's NOT IN?

Yes, exactly - thanks!

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 65 members 1,164 guests
Recent signins: