cancel
Showing results for
Did you mean:
Highlighted
Post Patron

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

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

8 REPLIES 8
Highlighted
Super User III

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Post Patron

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

Highlighted
Super User III

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Post Patron

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

Yes, exactly - thanks!

Highlighted
Frequent Visitor

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

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

Highlighted
Super User III

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

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

Highlighted
Anonymous
Not applicable

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

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.

Highlighted
Anonymous
Not applicable

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

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

it also doesn't work.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors