Forum Discussion
Advocate IISum on Case Statement on a column
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.
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
5 Replies
rnola16Thank 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.
Hi rnola16 ,
If the problem has been solved, please accept as solution the replies you find helpful.
Best regards,
Mengmeng Li
- Thaumaturgist
Helper I
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
)
) - rnola16
Thank you for prompt response. That didn't work, throws an error on 'in'. the columnname is a char field.
- kushanNa
Super User
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
