Power bi measure switch statement with examples

In this Power bi tutorial, we will discuss the Power Bi measure switch. And also we will discuss the below points.

Power bi measure switch statement
Power bi measure switch true
Power bi measure switch format
Power bi switch measure slicer
Power bi switch multiple measure

Table of Contents

Power bi measure switch

In power bi, switch() is used to evaluates an expression against a list of values and returns one of the multiple possible expressions of values.

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

Power bi measure switch statement

Here we will see how to use a switch statement to change the background color in the power bi table visual.

We will use the DimCustomer sample table, you can download it from here

Load the data using get data.
Now we will create a measure in Power BI which will change the background color by using switch statement.
Click on the new measure from the ribbon in power bi desktop.
Then write the below measure:

Background Color = 
SWITCH(
    SELECTEDVALUE(DimCustomer[EnglishEducation]),
    "Bachelors","Light Green",
    "High School","Orange",
    "Partial High School","Sky Blue",
    "Graduate Degree","Yellow",
    "White"
)

Now we will put this measure in the conditional formatting to change the color of background.
So, click on the Formatting section in the visualization pane.
Then expand the conditional formatting section.
Select the Field in my case it is EnglishEducation, then turn on the background color.
Then click on the Advanced control to apply conditional formatting.

Now background color for English education window will open, under Format by select Field value from the list.
Then under based on field select background color measure from the table. Click on OK.

Now to check the measure, select a table visual from the visualization pane.
Then in the value field drag and drop the fullname column, gender column and English eduction column from the field pane.

Read Power BI Measure IF with Examples

Power bi measure switch true

Here we will see an example of Power bi measure switch true. In the Power bi, switch true works row by row in the formula when the value test case evaluates as true, then that one gets matched.

We will use the below sample table to show the rank group which is under 10, 20, and 30, and if it is over 30 it will show over 30.

First, we will create a measure that will calculate the Sum of the overall store rank. Then we will use this measure to find the rank group using the measure.

Load the data using get data.
Click on the new measure from the ribbon in power bi desktop.
Then write the below measure.

Total store rank = SUM('Table'[Overall store rank])

Now we will find the rank group of store using switch true in the measure.
So, click o the new measure from the ribbon.
Then write the below measure:

Rank Group = 

SWITCH(
       TRUE(),
       [Total store rank]<10,"Under 10",
       [Total store rank]<20, "Under 20",
       [Total store rank]<30,"Under 30",
       "Over 30"
       )

Now to check the measure, select the table visual from the visualization pane.
Then in the value field, drag and drop the store column, overall store rank column and Rank group measure fromthe field pane.

Read Power BI Measure SUM – Complete tutorial

Power bi measure switch format

Here we will see how to use the format() in switch statement in power bi desktop. We will use the below sample table to format the sales cost to a currency like Euro, GBP, and USD.

And we will use the parameter table for currency which we will use in the slicer to switch the currency format.

Load the data using get data in power bi desktop.
Click on the new measure from the ribbon.
Then write the below measure:

Sales format Currency = 
var _sales=SUM('Fact Sales'[Sales cost])
return
FORMAT(_sales,
    SWITCH(
        SELECTEDVALUE('Currency'[Currency Type],"USD"),
        "USD","$#,##0.##",
        "GBP","£#,##0.##",
        "Euro","€#,##0.##"
        )
)

Now to check the measure, select the card visual from the visualization pane.
Then in the field, drag and drop the sales format currency measure from the field pane. By default it will show USD currency type.

Now create a slicer to switch the currency type, select the slicer from the visualization pane.
In the field, drag and drop the currency type column from the Currency table in the field pane.
And then select EURO from slicer, you can see the currency type get changed.

Read Power bi show items with no data

Power bi switch measure slicer

Here we will see an example of a Power bi switch measure slicer. We will use the financial sample data, which you can download from here.

From this sample data, we will use the country column and profit column.

In this example, we will use United States (assuming that the company is based in the United States of America) from the country column.

Now we have another table for a slicer, by selecting the selection we will retrieve all the values. In the slicer table, there is one column that contains two rows i.e. International and domestic.

Load the data using get data from the ribbon.
Now we will create the two measure which will calculate the total profit of domestic and international. Later we will use this two measure in switch case.
Click on the new measure from the ribbon.
Then write below measure for Domestic:

Domestic = CALCULATE(SUM('financials'[Profit]),'financials'[Country]="United States of America")

Now create an another measure for international, click on the new measure.
Then write the below measure:

International = CALCULATE(SUM('financials'[Profit]),'financials'[Country]<>"United States of America")

Now we will create a measure by using selected value and switch case.
SelectedValue() carry the selection table, when we select the one value i.e Domestic it get store in the var selection, then it return the Domestic profit.
If we select the international then it will return the international profit.
Click on the new measure from the ribbon.
Then write the below measure:

SlicerSelection = 

VAR Selection =

    SELECTEDVALUE ( 'Selection Table'[Selection] )

RETURN

    SWITCH (

        TRUE (),

        Selection = "Domestic", [Domestic],

        Selection = "International", [International],

        CALCULATE ( SUM ( 'financials'[Profit] ) )

    )

Now to check the measure, select the table visual from the visualization pane.
In the value field drag and drop the date column and slicer selection from the field pane.
Create a slicer which will switch or filter the value between domestic and international
In the field drag and drop the selection column from the selection table.
Then select the domestic in the slicer.

Read How to create a Measure based on Slicer in Power BI

Power bi switch multiple measure

Here we will see how to switch multiple measures in power bi desktop. We will use the order sample table, which you can download from here.

We have a measure name table that we will use in the slicer.

Then we will create a calculation to switch the measure and at last, we will create a dynamic title.

Load the data using get data in power bi desktop.
Now we will create a calculation to switch the measures.
Click on the new measure from the ribbon.
Then write the below measure.

SelectedMeasure =

IF(

HASONEVALUE(Slicer[Measure]),

SWITCH(

VALUES(Slicer[Measure]),

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

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

"Customers", DISTINCTCOUNT(Orders[Customer Name])

),

SUM(Orders[Sales])

)

In the above measure, if () will check whether the first argument is true, and then it will return accodingly.
HASONEVALUE() will return true when the column has been filtered down to one distinct value only.
Switch () is similar to an IF/ELSEIF function. So, in switch (), if the expression return value 1 then it evaluates expression 1 and if the expression return value 2, then it evaluates expression 2 and so on.
In the Value(), if the input is a column name, then it return distinct value from that column and if the input is a table name and then it returns rows from that table.
So, in slicer if we click on profit then it will return profit and if we click on customer then it will show number of customer.

Now to check the measure, select the stacked column chart, in the axis field drag and drop the category column from the field pane.
In the values field, drag and drop the SelectedMeasure measure from the field pane.

Now create a slicer by which we will switch the measure in the stacked column chart.
In the field, drag and drop the Measure column from the field pane.
Then select the measure from the slicer, it will show you the measure by category.

Now there is a problem when we select a measure from the slicer, it is showing “SelectedMeasure by category”, so what we need is when we select sales measure from the Slicer it will show” sales by category”.

We can change the title dynamically by creating a simple measure and then adding it to the formatting pane’s Title section in the Power bi.

So, click on the new measure Form the ribbon.
Then write the below measure:

Measure Title = 

IF(

HASONEVALUE(Slicer[Measure]),

VALUES(Slicer[Measure]),

"Sales"

)

& " by Category"

Now in the visualization pane, click on the Formatting section.

Now expand the title section, then click on fx under title text.

Now title text window will open, format by field value, then under Based on field, select Measure title measure.
Then click on Ok.

Now when you click on the slicer, it will show the name of measure by category.

You may like the following Power BI tutorials:

In this Power bi tutorial, we discuss the Power bi Dax measure switch with examples. And also we discuss the below points:

How to use Power bi measure switch statement
Power bi measure switch true
Power bi measure switch format
Power bi switch measure slicer
Power bi switch multiple measure

Bhawana Rathore

Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com