DAX equivalent of SQL's NOT IN?

[mmace1]

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!

[Anil_Mahanty]

Card Sales =
CALCULATE (
    [total valid spend],
    FILTER ( payments, payments[paymentypeid] = 7 && not payments[paymentprocessorID] IN { 1, 5, 6, 9 } )
)

[sqldev2017]

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

[Anonymous]

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]

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 · Accepted solution]

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

[arneh]

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

[MFelix]

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.

[mmace1]

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!

[MFelix]

Hi @mmace1,

Nice catch on the typo. 

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

Regards,

MFelix

[mmace1]

Yes, exactly - thanks!