cancel
Showing results for 
Search instead for 
Did you mean: 
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

9 REPLIES 9
sqldev2017
Microsoft
Microsoft

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors