Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mmace1
Impactful Individual
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
MFelix
Super User
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.

 

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

10 REPLIES 10
Anil_Mahanty
Frequent Visitor

Card Sales =
CALCULATE (
    [total valid spend],
    FILTER ( payments, payments[paymentypeid] = 7 && not payments[paymentprocessorID] IN { 1, 5, 6, 9 } )
)
sqldev2017
Microsoft Employee
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.

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.

 

Anonymous
Not applicable

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.
 
MFelix
Super User
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.

 

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



arneh
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

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

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



mmace1
Impactful Individual
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!

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



mmace1
Impactful Individual
Impactful Individual

Yes, exactly - thanks!

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors