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:

Power BI Switch function example
Power BI Switch function example

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"
)
Example of Switch() function in Power BI
Example of Switch() function in Power BI

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")
Difference between IF and Switch function on Power BI
Difference between IF and Switch function on Power BI

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.

Power bi Dax Switch
Power BI Dax Switch

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:

Power bi Dax Switch
Power bi Dax Switch

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:

Power BI DAX Switch Statement
Power BI DAX Switch Statement

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.

Power BI DAX Switch multiple conditions
Power BI DAX Switch multiple conditions

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.

Power BI DAX Switch using multiple conditions
Power BI DAX Switch using multiple conditions

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.

Power BI DAX Switch multiple conditions example
Power BI DAX Switch multiple conditions example

Example-4:

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

Example of Power BI DAX Switch multiple conditions
Example of Power BI DAX Switch multiple conditions

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:

 Power BI DAX Switch true
Power BI DAX Switch true

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"
       )
Power BI DAX switch true statement
Power BI DAX switch true statement

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:

Power bi switch format()
Power bi switch format()

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.

format() with Power bi switch
format() with Power bi switch

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.

Dynamic formatting on Power BI Dax Switch
Dynamic formatting on Power BI Dax Switch

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:

Example of Switch with Format() in Power BI
Example of Switch with Format() in Power BI

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:

Power BI DAX Switch Slicer
Power BI DAX Switch Slicer

Then we will create a slicer visual using this data:

Power BI DAX Switch Slicer
Power BI DAX Switch Slicer

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.

Power BI DAX Switch Slicer
Power BI DAX Switch Slicer

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.

Power BI DAX Switch Slicer
Power BI DAX Switch Slicer

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:

Power BI DAX Switch multiple measure
Power BI DAX Switch multiple measure

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.

Power BI DAX Switch multiple measure
Power BI DAX Switch multiple measure

Step-3:

Now we will create a slicer using the measure table:

Power BI DAX Switch with measure
Power BI DAX Switch with measure

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

Power BI DAX Switch with measure
Power BI DAX Switch with measure

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

DAX Measure on Power BI Switch
DAX Measure on Power BI Switch

Step-4:

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

DAX Measure on Power BI Switch
DAX Measure on Power BI Switch

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:

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?
>