Power BI Switch – DAX function

Do you want to work with SWITCH on Power BI using DAX? Please check out this detailed guide to get all the details as well as ideas about Power BI SWITCH DAX function. Let us try to understand, how to use the Power BI Switch function with the below topics:

• Power BI DAX Switch
• Difference between IF and Switch in Power BI DAX
• Power BI DAX Switch statement with example
• Power BI DAX Switch multiple conditional Statement
• Power BI DAX Switch true
• Power BI DAX Switch using multiple measure
• Power BI DAX Switch format
• Power BI DAX Switch Slicer

Power BI DAX Switch

First, we will know what is a Switch function in Power BI, how it works, and what is its syntax? In Power BI, Switch is a kind of logical concept that evaluates an expression against a list of values and returns one of the multiple possible result expressions.

The syntax is:

Measure = SWITCH (<expression>, <value>, <result>[,<value>, <result>]...[,<else>])

Let’s have a look at the below example, how the Power BI Switch function works:

Here we have created a table in Power BI having two columns with some values:

Now we will create a Power BI measure that will calculate the sum of the value, which will use as an expression.

Total = CALCULATE(SUM('Table (2)'[Value1]) + SUM('Table (2)'[Value2] ))

Again we will create another measure that will justify the Switch function:

Switch_measure = SWITCH (
    [Total],
    3, "Three",
    4, "Four",
    5, "Five", 
    "Above five"
)

Read Get Month Name from Month Number in Power BI

Difference between IF and Switch in Power BI DAX

On the basis of expression, there is a difference between IF-else and Switch statement i.e. if-else statement uses multiple statements for multiple choices whereas a Switch statement uses a single expression for multiple choices.

In Power BI, an If-else statement evaluates integer, character, boolean type but in the case of Switch, it evaluates character and integer values only.

In the case of the If-else statement checks a condition, and returns the first value as TRUE if it matches with the condition. Otherwise, it returns the Else value. But in the case of the switch statement, it returns different results depending on the value of an expression.

As we already see, how the measure works on Power BI. Now we will see how an IF function works on Power BI. For this, here we are going to use this same example that we have created for the switch function.

To see how an IF function works on Power BI DAX, we have to create a measure that will calculate if the sum is greater than 5 then it will return UP otherwise it will return a false value i.e. Down

if_measure = IF([Total] > 5, "UP", "Down")

We can see the difference between IF and Switch functions using Power BI DAX.

Read Power BI convert yyyymmdd to date

Power BI DAX Switch statement with example

Let’s take an example to implement the Power BI Switch statement or how a switch statement works.

For this, we have prepared a SharePoint list based on the Product’s Order.

Now we will import this SharePoint list to Power BI. For this, on Power BI desktop > get data > SharePoint Folder > give ur site URL. It will connect the SharePoint list to Power BI. Else you can download this sample data from here (as we export this data to Excel).

We can see our data is being loaded on Power BI. Using this data we will create a table like below:

Now we will create a measure using the Switch statement, that will return the statements according to our condition. For example, it will return the product delivery report according to the product’s category.

Delivery Report = 
SWITCH(
    SELECTEDVALUE(Orders[Product Category]),
    "Office Supplies","2-3 Working days",
    "Technology","By Tomorrow",
    "With in 1 Week"
)

Let’s use this measure on our table to show the result:

From the above example, we can see how a Switch statement works on Power BI using DAX.

Read Contact your admin to enable embed code creation Power bi

Power BI DAX Switch multiple conditional statements

As we have already described, a Switch function allows multiple condition statements. Let’s discuss how to implement the Power BI DAX Switch multiple conditional statements. According to multi-conditional statements, it will change the background color.

Here is a step-by-step guide for this:

Step-1:

Using the sample data, we will create a table using Product category and customer segment.

Step-2:

Now we will create a measure using Switch, that will change the background color of a column(i.e. Customer segment) according to multiple conditions.

Background_Color = 
SWITCH(
    SELECTEDVALUE(Orders[Customer Segment]),
    "Consumer","Yellow",
    "Home Office","Red",
    "Small Business","LightBlue",
    "White"
)

Step-3:

Then we will go to the formatting pane > expand the Conditional formatting > Turn ON the Background color.

We can use this measure in any column that is used in the table by expanding the down arrow under the conditional formatting.

After turning ON, It will open a page, where we can set the conditions on our chart.

Example-4:

Then press OK to apply conditions and we can see the table will look like below:

We can see, the background color is applied on the table according to multiple conditions using SWITCH in DAX.

This is how a Power BI DAX Switch works with multiple conditions.

Read How to use weekday function power bi with example

Power BI DAX Switch true

In Power BI DAX Switch, a true is an expression that works like a nested IF statement, which checks row by row in the formula. It returns a result when our calculation is true according to expression.

Let’s see how a TRUE statement works with SWITCH on Power BI.

For this, first, we will create a table using sample data as our example. We have created a table like this:

Now we will create a measure that will calculate the order priority according to the order quantity of the products.

Priority = SWITCH(
       TRUE(),
       [Total Quantity]<1000,"good",
       [Total Quantity]<1500, "Better",
       [Total Quantity]<2000,"Best",
       "Execellent"
       )

The above table showing us the calculated value of whether it is good, better, best, or excellent according to conditions.

This is how a true statement works with a SWITCH statement.

Read What is the difference between calculated column and measure in Power BI

Power BI DAX Switch format

Format() is one of the most powerful and simple functions in Power BI DAX. It converts a value to text according to the specified format.

The syntax for this function is:

format(<value>,<format-string>[,<local name>])

Now we will see how format() function works with SWITCH in Power BI DAX with a suitable example.

Step-1:

Using our sample data, we are going to create a table like the below:

Step-2:

Now we will create another data table having a column with some currency format like Rupee, GBP, USD, Euro, etc. Then we will create a slicer chart using this currency format.

Step-3:

We will create a measure that will format the total sales according to slicer choices:

Sales = 
var _sales=SUM(Orders[Sales])
return
FORMAT(_sales,
SWITCH(
SELECTEDVALUE('Currency'[Currency format],"USD"),
"Rupee"," ₹#,##0.##",
"USD","$#,##0.##",
"GBP","£#,##0.##",
"Euro","€#,##0.##",
"YUAN"," ¥#,##0.##"
)
)

And use this measure on Card visual to show the result.

By default, it is showing the result in USD/Dollar format. If we select a choice from the slicer, then it will appear the result like below:

This is how we can use format function with Switch in Power BI DAX.

Read How to create and use Power BI Bookmarks

Power BI DAX Switch measure Slicer

Now we will see how a switch works with a Slicer visual to get the selected value. Using our sample data we will implement this with an example.

First, we will create a table having a column with 2 options like below:

Then we will create a slicer visual using this data:

Now we will create two measures for these selection options to activate these selection options.

For Global:

Global = CALCULATE(SUM('Orders'[Sales]),'Orders'[Product Category]<>"Furniture")

For Private:

Private = CALCULATE(SUM('Orders'[Sales]),'Orders'[Product Category]= "Furniture")

Again we will create another measure using these measures which will calculate and visualize the data according to our selective options on Slicer:

SlicerSelection = 
VAR Selection =
    SELECTEDVALUE ( 'SelectionTable'[Selection] )
RETURN
    SWITCH (
        TRUE (),
        Selection = "Private", [Private],

        Selection = "Global", [Global],
        CALCULATE (SUM('Orders'[Profit]))
        )

In the above code, we are using SWITCH(TRUE()) to get the true value which is a calculated value of the slicer’s selective value. Also, we are using a variable as Selection to avoid the use of Selectedvalue() function multiple time.

Now we will create a line chart or you can create any chart to show this measure value. When we select Global it will show only those data which goes under Global.

In line chart, we are using the month data field from our sample data and the measure that we created. If we select Private, then it will show the data according to that.

On this way we can show our selective data using Slicer by Power BI DAX Switch.

Read Remove blank from Power bi slicer

Power BI DAX Switch using multiple measure

Here we will see how a Power BI switch function is use in multiple measure. For this here we are going to use our sample data and follow this step-by-step guide to implement this:

Step-1:

First, we will create a table having a column with some field values like below:

Step-2:

Now we will create a measure to activate the functionalities of these field values:

Selected_Measure = 

IF(

HASONEVALUE('Measure Table'[Measures]),

SWITCH(

VALUES('Measure Table'[Measures]),

"Sales", SUM(Orders[Sales]),

"Profit", SUM(Orders[Profit]),

"order Quantity", SUM(Orders[Order Quantity]),

SUM(Orders[Sales])

))

Here we are using HASONEVALUE(), that returns a true value when the column has been filtered down to one distinct value, otherwise it returns false.

Value() is used to converts a text string represents a number to a number i.e. if we input a column name, then it return as a distinct value. If we use a table name as an input thenit will return the rows as a value.

Now we will create a visual using this measure and product’s category from our sample data. For example, we are going to use a clustered bar chart.

Step-3:

Now we will create a slicer using the measure table:

We can see when we select any option from the slicer it won’t change the title of the chart. But we want to change the title of the chart dynamically according to the selective option of the slicer. or this we will create another measure:

Measure Title = 
IF(
HASONEVALUE('Measure Table'[Measures]),
VALUES('Measure Table'[Measures]),"Sales") & " by Category"

To use this measure, go to format pane > turn on title > click on conditional function “fx”

By clicking on “fx“, it will open a page to do conditional formating. Here we will use the measure that we have created i.e. Measure title

Step-4:

Then click on OK. Now we can see the title will change dynamically according to slicer choice.

In this way, by using multiple measures we can work with the Power BI Dax Switch function.

You may like the following Power BI tutorials:

• Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax
• Subtraction in Power bi using DAX
• Power BI Card – How to Use + Examples
• Power BI Measure Sum and Subtract Example
• Embed Power BI report in SharePoint Online
• Power BI IF
• How to sort by multiple columns in Power BI

Conclusion

From this Power BI Tutorial, we learned all about Power BI Dax Switch. Also, we discussed the below topics:

• What is Power BI DAX Switch?
• What are the difference between IF and Switch in Power BI DAX with example?
• How a Switch statement works on Power BI DAX?
• How to works with multiple conditional Statement on Power BI DAX Switch?
• How to use true on Power BI DAX Switch?
• how a Power BI DAX Switch using multiple measure?
• Do format using Power BI DAX Switch?
• How to use Power BI DAX Switch Slicer?