POWER BI: Modeling

SWITCH Function

Evaluates an expression across a list of values. Returns different results depending on whether the expression matches the value.

The SWITCH function can be used in place of writing multiple IF THEN statements.

Using SWITCH for specific values

The SWITCH function makes it easy to write expressions with multiple IF THEN conditions. Rather than multiple nested IF statements, using SWITCH makes things easier and more readable. SWITCH is very similar to the CASE statement used in VBA and many other languages.

Example: Transform product ids into product names

                     product_name = SWITCH(TransactionData[product_id],
                                            "Candy A", "KitKat",
                                            "Candy B", "JellyBelly",
                                            "Candy C", "Maynards"
                                            )
	            

We have some generic names such as "Candy A" under out Product ID column. (note you should not have these kind of names for you product ids!). In any case, we want to evaluate all of our rows which have the generic name "Candy A" and rename it to "KitKat". Similarly, if it is "Candy B" then "Jelly Belly" and if it is "Candy C" then "Maynards".

Using SWITCH for a range of values

You can also evaluate inequalities using the SWITCH function. For example, perhaps we want to have show different statements depending on the value of ROI. In POWER BI we write:

                      ROI Range = SWITCH(
                                    TRUE(), 
                                    AND([ROI]>=0, [ROI]<=0.6), "Between 0% and 60%",
                                    AND([ROI]>0.60, [ROI]<=0.7), "Between 60% and 70%",
                                    AND([ROI]>0.70, [ROI]<=0.8), "Between 70% and 80%",
                                    AND([ROI]>0.80, [ROI]<=0.9), "Between 80% and 90%",
                                    AND([ROI]>0.90, [ROI]<=1.0), "Between 90% and 100%",
                                    "greater than 100%"
                                    )
	            
  • The returned result is the first value that is True.
  • ROI is a created measure

We could use this to show projections. For instance, change ROI to week1 ROI. A SWITCH statement could then output different values base off week 1 ROI.

                      ROI Range = SWITCH(
                            TRUE(), 
                            AND([ROI Week 1]>=0, [ROI]<=0.6),   "Projected ROI after 1 Month: Under 80%",
                            AND([ROI Week 1]>0.60, [ROI]<=0.7), "Projected ROI after 1 Month: Between 80% and 95%",
                            AND([ROI Week 1]>0.70, [ROI]<=0.8), "Projected ROI after 1 Month: Between 95% and 110%",
                            AND([ROI Week 1]>0.80, [ROI]<=0.9), "Projected ROI after 1 Month: Between 100% and 130%",
                            AND([ROI Week 1]>0.90, [ROI]<=1.0), "Projected ROI after 1 Month: Between 120% and 140%",
                                                                "Projected ROI after 1 Month: Between 125% and 160%"
                            )
	            
Try It Out

It is good practice to always specify a default value, if none of the conditions are true. In this case the default value Projected ROI after 1 Month: Between 120% and 140% is shown when ROI is above 100%.

See also: Video on Switch Function

Up Next: Making a Date Table.


Dash-Intel is a Power BI and Tableau resource site for data visualization and building BI dashboards.

Data Analyst & Consultant

Copyright 2015-2023 Dash-Intel.com Terms