Multiple Case Statement in a measure

[Anonymous]

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

[amitchandak · Accepted solution]

@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

[AllisonKennedy]

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.