Forum Discussion
How to replace nested IF calculation for New Column with using switch
This nested if calculation DOES work fine, but I have hard time replacing this using SWITC function? I amm affraid that if I have much longer nested calc it might be safer to use switch instead. Can you please show me how would this nested calc (to define products Prod1-Prod16) using my Product from table Query1, could be writen using SWITC function instead. Thanks so much in advance!
Product = if(Query1[PRODUCT]="20","Prod1", if(Query1[PRODUCT]="CC-20b","Prod2", if(Query1[PRODUCT]="21","Prod3", if(Query1[PRODUCT]IN {"31","43","50","53","54","96","98"},"Prod4", if(Query1[PRODUCT]="12","Prod5", if(Query1[PRODUCT]IN {"CC-50b","CC-50d","CC-50n","CC-50p"},"Prod6", if(Query1[PRODUCT]="58","Prod7", if(Query1[PRODUCT]IN {"10","13"},"Prod8", if(Query1[PRODUCT]IN {"65","68"},"Prod9", if(PRODUCT]="CC-60","Prod10", if(Query1[PRODUCT]IN {"42","60","75","99","EXIT"},"Prod11", if(Query1[PRODUCT]="64","Prod12", if(Query1[PRODUCT]IN {"61","RE-61"},"Prod13", if(Query1[PRODUCT]IN {"70","71","73","74"},"Prod14", if(Query1[PRODUCT]IN {"52","8"},"Prod15", if(Query1[PRODUCT]IN {"51","55","82","9"},"Prod16"))))))))))))))))
I believe this would be better done in Power Query using conditional columns. To answer your question, you could use switch/true
=SWITCH(TRUE(),
Query1[PRODUCT]="20","Prod1",
Query1[PRODUCT]="CC-20b","Prod2",
etc
also, use daxformatter.com to make it easier to read.
4 Replies
I believe this would be better done in Power Query using conditional columns. To answer your question, you could use switch/true
=SWITCH(TRUE(),
Query1[PRODUCT]="20","Prod1",
Query1[PRODUCT]="CC-20b","Prod2",
etc
also, use daxformatter.com to make it easier to read.
Thanks a lot Matt! Much apprecite your time and help, and the suggestion to check daxformatter.
Product = SWITCH(
TRUE(),
Query1[PRODUCT] IN { "20"} , Prod1,
....
Query1[PRODUCT] IN { "31","43","50", etc} , Prod4,
....
Else statement if needed)Product = SWITCH(
TRUE(),
Query1[PRODUCT] IN {"20"} , "Prod1",
....
Query1[PRODUCT] IN {"31","43","50","53","54","96","98"},"Prod4",
.....
Else statement if needed)
