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],
    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!

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

10 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    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

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

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago

        Hi mmace1,

         

        Nice catch on the typo. 

         

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

         

        Regards,

        MFelix

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        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.

  • sqldev2017's avatar
    sqldev2017
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

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

  • 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.
       
  • Anil_Mahanty's avatar
    Anil_Mahanty
    Frequent Visitor
    3 years ago
    Card Sales =
CALCULATE (
    [total valid spend],
    FILTER ( payments, payments[paymentypeid] = 7 && not payments[paymentprocessorID] IN { 1, 5, 6, 9 } )
)