Sum on Case Statement on a column

[rnola16]

I'm trying to use this SQL statement in DAX func.

=Sum( CASE when tablename.colname in ('a','b') then -1

                      when tablename.colname in ('c','d') then 0

                      when tablename.colname in ('e','f') then 1

             else 0 

             END)

which is an ideal func to use CALCULATE() or SWITCH() ?

Thanks. 

[rnola16]

Thank you, that worked. The mistake I did, i tried creating a new column in the power query and didnt work, instead created a new measure on the report page.

[Anonymous]

Hi @rnola16 ,

If the problem has been solved, please accept as solution the replies you find helpful.

Best regards,

Mengmeng Li

[rnola16]

Thank you for prompt response. That didn't work, throws an error on 'in'. the columnname is a char field.

[kushanNa · Accepted solution]

Works for me 

maybe you are trying to use DAX code in M (Power Query) then it will throw an error on 'in' ? if that's the case then DAX functions cannot be used in M query 

[Thaumaturgist]

I'd recommend SWITCH. Give this a try:

SUMX(
tablename,
SWITCH(
TRUE(),
tablename[colname] IN {"a", "b"}, -1,
tablename[colname] IN {"c", "d"}, 0,
tablename[colname] IN {"e", "f"}, 1,
0
)
)