cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
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
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.

 

notin.png

 

Regarrds,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

8 REPLIES 8
Highlighted
Super User III
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.

 

notin.png

 

Regarrds,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Post Patron
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Post Patron
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

https://stackoverflow.com/questions/45945815/how-to-connect-google-sheet-with-power-bi

Highlighted
Super User III
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


Did I answer your question? Mark my post as a solution!

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.

cars without service = CALCULATE (VALUES ('3A_CHAMADO_SERVICOS_CAL_VIEW (2)' [License Plate]);
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(
VALUES(GWT_CARTAO_EQUIPAMENTO[Placa])) in VALUES('3A_CHAMADO_SERVICOS_CAL_VIEW (2)'[Placa])))
 
 
it also doesn't work.
 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

Upcoming Events

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

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors