Forum Discussion
Multiple criteria for switch statement
I'm really struggling to get my head round this SWITCH statment, may not be possible in one calculated column? I've written it in SQL below as a case statment and it works but cant do the same in DAX. I believe SWITCH would be the best function to use and have tried nesting IF statetments in there as well with no luck.
Any help greatly appreciated!
case when A is null and B in ('1',2','3','4','5') then 'Global'
when A in ('11',12','13') and B is null or B = '6' then 'Local'
when A is null and B is null or B = '6' and DIM.C not in ('98','99') then 'Customer'
when A is null and B is null or B = '6' and DIM.C = '98' then 'Other' else 'NA' end as 'Category'
Hi Anonymous
This is a formula for a calculated columnSwitch Statement = SWITCH ( TRUE (), TableName[A] = BLANK () && TableName[B] IN { 1, 2, 3, 4, 5 }, "Global", TableName[A] IN { 11, 12, 13 } && OR ( TableName[B] = BLANK (), TableName[B] = 6 ), "Local", TableName[A] = BLANK () && OR ( TableName[B] = BLANK (), TableName[B] = 6 ) && NOT ( RELATED ( DIM[C] ) IN { 98, 99 } ), "Customer", TableName[A] = BLANK () && OR ( TableName[B] = BLANK (), TableName[B] = 6 ) && RELATED ( DIM[C] ) = 98, "Other", "NA" )
2 Replies
- tamerj1
Community Champion
Hi Anonymous
This is a formula for a calculated columnSwitch Statement = SWITCH ( TRUE (), TableName[A] = BLANK () && TableName[B] IN { 1, 2, 3, 4, 5 }, "Global", TableName[A] IN { 11, 12, 13 } && OR ( TableName[B] = BLANK (), TableName[B] = 6 ), "Local", TableName[A] = BLANK () && OR ( TableName[B] = BLANK (), TableName[B] = 6 ) && NOT ( RELATED ( DIM[C] ) IN { 98, 99 } ), "Customer", TableName[A] = BLANK () && OR ( TableName[B] = BLANK (), TableName[B] = 6 ) && RELATED ( DIM[C] ) = 98, "Other", "NA" )