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