Forum Discussion
Multiple Case Statement in a measure
Hi I need help with creating a measure for below case statement. I am using direct query so unable to create a calculate column.
sum(case when Table1[field1] in (10, 12, 13, 33, 35, 37, 39) THEN 0 when Table1[field1] in (71, 75) THEN Table1[amt] when Table2[name] = 'AB' THEN [amt] else 0 end) as 'Gross'
Appreciate if you could help with getting the measure for the above case statement
Anonymous , You can try like this. But You can not simply use Table 2 in table 1. That will depend on relation .
sumX(Table1 , Switch(True(), Table1[field1] in {10, 12, 13, 33, 35, 37, 39 } , 0 ,
Table1[field1] in {71, 75} , Table1[amt] ,
Table1[name] = "AB" , Table1[amt] , 0
)or
sumX(Table1 , Switch(True(), Table1[field1] in {10, 12, 13, 33, 35, 37, 39 } , 0 ,
Table1[field1] in {71, 75} , Table1[amt] ,
related(Table2[name]) = "AB" , related(Table2[amt]) , 0
)Refer to my Video on Switch : https://www.youtube.com/watch?v=gelJWktlR80&pbjreload=101
2 Replies
You can use SWITCH(TRUE() for a case
Gross =
SWITCH(TRUE()
, SELECTEDVALUE(Table1[field1]) in (10, 12, 13, 33, 35, 37, 39) , 0
, SELECTEDVALUE(Table1[field1]) in (71, 75) , SUM(Table1[amt])
, SELECTEDVALUE(Table2[name]) = 'AB' , SUM([amt])
, 0 )
Anonymous note it doesn't provide the row context, so you'll still need to provide that in the visual as you're using direct query.
Anonymous , You can try like this. But You can not simply use Table 2 in table 1. That will depend on relation .
sumX(Table1 , Switch(True(), Table1[field1] in {10, 12, 13, 33, 35, 37, 39 } , 0 ,
Table1[field1] in {71, 75} , Table1[amt] ,
Table1[name] = "AB" , Table1[amt] , 0
)or
sumX(Table1 , Switch(True(), Table1[field1] in {10, 12, 13, 33, 35, 37, 39 } , 0 ,
Table1[field1] in {71, 75} , Table1[amt] ,
related(Table2[name]) = "AB" , related(Table2[amt]) , 0
)Refer to my Video on Switch : https://www.youtube.com/watch?v=gelJWktlR80&pbjreload=101
