Do you ever get frustrated when you have to nest multiple conditions using the IF function in Excel? If YES, you are not alone on the table.
Let me introduce you to the Switch Function in PowerBI and MS Excel.

Requirements and Availability:

It is recommended that you have a basic understanding of Excel, and Power BI DAX to get the most out of this tutorial, and data entry forms are supported in Excel 365, Excel 2019, Excel Online (Excel for the web), and PowerBI DAX. 

The SWITCH Function:

The SWITCH function evaluates one value (called the expression) to a list of values and returns the first matched value as the result. If no match is found, a default value may be returned as an option. Let me walk you through on how to use Wwitch Function in PowerBI and MS Excel.

Overview:

The Switch function can be expressed in the simplest form as

=SWITCH(expression, val1, result1, [val2, result2],....,[default])

Check out the YouTube Video:

 

The following parameters are passed to the SWITCH function:

• Expression: This is the required argument, It could be a number, a date, or a piece of text that the function compares.
• Value: This is the value against which the expression will be compared.
• Return: When the matching value parameter matches the expression, this is the value that will be returned.
• Default (Optional): In Excel and PowerBI, whenever you see a square bracket with a list of formulas it means the session is optional. The default value if no matches are discovered in the value expression, is the value that the function should return.

Application of SWITCH function for Classification Purpose in Excel and PowerBI

• From the table below

Animal	Categories
Cat	Canine
Dog	Pet
Lion	King

From the table above you are expected to change every animal to its respective category. By using the SWITCH function I would show you how this can be achieved in both Excel and PowerBI.

Excel:

=SWITCH(A2,"Cat","Canine","Dog",Pet","King")

Take a look at the result below:

The same procedure would be repeated for PowerBI:

Categories = SWITCH('Animal Logical Condition'[Animal],
	"Cat","Canine",
	"Dog","Pet",
	"King"
	)

Application of SWITCH function for Range values in Excel and PowerBI

For the Range value we are expected to group the following age range:
0-16:”Teen”, 17-22:”Youth”, 23-49:”Adult”,50-Above:”Elder”

Excel:

=SWITCH(TRUE,B6<=16,"Teen",B6<=22,"Youth",B6<=49,"Adult","Elder")

The same procedure would be repeated for PowerBI:

Age Range = SWITCH(TRUE(),
	Multiple[Age]<=16,"Teen",
	Multiple[Age]<=16,"Youth",
	Multiple[Age]<=16,"Adult",
	"Elder"
)

BONUS 🎁🎁🎁

IFS FUNCTION:

The IFS function checks whether one or more conditions are met and returns the first TRUE condition’s value. With several criteria, IFS can replace multiple nested IF statements and is considerably easier to read.

Formula:

= IFS(logical_test1, Value1 [logical_test2, Value2] ..., [logical_test127, Value127])

The following parameters are passed to the IFS function:

• Logical_test1: This is a mandatory argument, and it is the condition that Excel uses to determine if the value is TRUE or FALSE.
• Value1: When logical test1 is true, this is the result.

Application of IFS function for Classification Purpose
Using the IFS function for categoring items

=IFS(A2="Cat","Canine",A2="Dog","Pet",TRUE,"King")

Using the IFS function for groping range values:

=IFS(B6<=16,"Teen",B6<=22,"Youth",B6<=49,"Adult",TRUE,"Elder")

Further Study:

• UrBizEdge Youtube Channel
• UrBizEdge Blog Site.
• Microsoft Documentation on SWITCH and IFS Functions.

Connect with me on Twitter and LinkedIn, happy learning guys!!!