cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Impactful Individual

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

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

10 REPLIES 10
Frequent Visitor
```Card Sales =
CALCULATE (
[total valid spend],
FILTER ( payments, payments[paymentypeid] = 7 && not payments[paymentprocessorID] IN { 1, 5, 6, 9 } )
)```
Microsoft Employee

How hard is it to have "Not IN" ?   At least for the sake of consistency !

Anonymous
Not applicable

I have a similar problem.
I want to return the plates that do not appear in this other table. However, the error below appears, follow my formula of the measure.

FILTER (GWT_CARTAO_EQUIPAMENTO; NOT (
VALUES (GWT_CARTAO_EQUIPAMENTO [Plate])) in {VALUES ('3A_CHAMADO_SERVICOS_CAL_VIEW (2)' [Plate]))))

Helpp

Error Message:
MdxScript(Model) (37, 44) Erro de cálculo na medida '_Medidas'[carros sem serviço]:Uma tabela de vários valores foi fornecida, sendo que um único valor era esperado.

Anonymous
Not applicable
or COUNTROWS
carros sem serviço = CALCULATE(COUNTROWS(VALUES(GWT_CARTAO_EQUIPAMENTO[Placa]));
FILTER(GWT_CARTAO_EQUIPAMENTO; NOT(

it also doesn't work.

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

I solved this by using excel as a source. Following this method I don't have to work with HTML code in my power query to get all the sheets

Super User

Hi @arneh,

Believe that you have make this answer on the incorrect post. This post has nothing to do with linking to google sheets or excel.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Impactful Individual

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

Hi @mmace1,

Nice catch on the typo.

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

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Impactful Individual

Yes, exactly - thanks!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors