Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Case statement
DAX
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
5 years ago

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.