NOT IN dax showing wrong counts

[Anonymous]

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. 

[v-yalanwu-msft · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Replace || with &&.  Remove the redundant condition - C[S]<>"C"

[Anonymous]

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]

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]

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]

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! 😊

[amitchandak]

@Anonymous , Try like

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