Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mk60
Resolver I
Resolver I

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!

Mk60_0-1718219680659.png

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"))))))))))))))))

1 ACCEPTED SOLUTION

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

4 REPLIES 4

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks a lot Matt! Much apprecite your time and help, and the suggestion to check daxformatter. 

akoren2
Frequent Visitor

Product = SWITCH(
                     TRUE(),
                            Query1[PRODUCT] IN {"20"} , "Prod1",
                            .... 
                           Query1[PRODUCT] IN {"31","43","50","53","54","96","98"},"Prod4",
                           .....
                           Else statement if needed

                           )

akoren2
Frequent Visitor

Product = SWITCH(

                         TRUE(),
                          Query1[PRODUCT] IN { "20"} , Prod1,
                          ....
                          Query1[PRODUCT] IN { "31","43","50", etc} , Prod4,
                          ....
                          Else statement if needed)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors