Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

NOT IN dax showing wrong counts

I am trying to write a DAX statement for NOT IN but i don't know the syntax for it. It's like:

 

CALCULATE(sum(C[T_S]),C[S] <> "C" || C[S] <> "V" || C[S] <> "D" || C[S] <> "C")
 
Eventhough, I mentioned not equals in the dax, the statement is showing me the total counts not the counts where there is <>.
 
Any help is much appreciated. 
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    You could change to it.

    q =
DIVIDE (
    CALCULATE (
        SUM ( C[T_S] ),
        FILTER ( C, NOT ( C[S] ) IN { "C", "V", "D", "C" } )
    ),
    SUM ( T_D[T_S] )
)


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Try like

    CALCULATE(sum(C[T_S]), Filter(C, not( C[S] in {"C" ,V","D","C"} ) ) )

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I tried to put the formula but it says "too few arguments were passed to the divide function. thhe minimum argument count for the functions is 2". 

     

    DIVIDE(CALCULATE(sum(C[T_S]), Filter(C, not( C[S] in {"C" ,"V","D","C"}))))
    not sure, where i am wrong.
    • rohit_singh's avatar
      rohit_singh
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hi Anonymous,

      You have missed out argument 2 in the DIVIDE function. You have only specified the numerator in the function.

      DIVIDE(CALCULATE(sum(C[T_S]), Filter(C, not( C[S] in {"C" ,"V","D","C"}))) , ____   )

       

      If this solves your issue, please mark amitchandak 's answer as the solution to help others too.

       

      Kind regards,

       

      Rohit

      Appreciate your kudos! ğŸ˜Š

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    So, I wrote the denominator but the power bi is saying that "Operator or expression '( )' is not supported in this context."  

    The italic, underline part is showing error as: "Operator or expression '( )' is not supported in this context."  

    DIVIDE(CALCULATE(sum(C[T_S]), Filter(C, not(C[S] in {"C" ,"V","D","C"},SUM(T_D[T_S]),0))))
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      so, I wrote the denominator but the power bi is saying that "Operator or expression '( )' is not supported in this context."  

      The italic, underline part is showing error as: "Operator or expression '( )' is not supported in this context."  

      DIVIDE(CALCULATE(sum(C[T_S]), Filter(C, not(C[S] in {"C" ,"V","D","C"},SUM(T_D[T_S]),0))))
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous ;

    You could change to it.

    q =
DIVIDE (
    CALCULATE (
        SUM ( C[T_S] ),
        FILTER ( C, NOT ( C[S] ) IN { "C", "V", "D", "C" } )
    ),
    SUM ( T_D[T_S] )
)


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.