Forum Discussion

mmace1's avatar
mmace1
Icon for Impactful Individual rankImpactful Individual
7 years ago
Solved

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], ...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago
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.