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

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!

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.
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.
Resolver I

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

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

)

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors