Table of Contents
A case statement in Power BI is a way to create a conditional expression that evaluates multiple conditions and returns a specific value based on the result. It is used to categorize data or perform calculations based on different criteria. To write a case statement in Power BI, you can use the SWITCH function, which allows you to specify multiple conditions and corresponding values. For example, you can create a case statement to categorize sales data into different regions based on the country column. This allows you to easily analyze and visualize the data based on the regions.
A case statement is a type of statement that goes through conditions and returns a value when the first condition is met.
The easiest way to implement a case statement in Power BI is by using the SWITCH function in DAX, which uses the following basic syntax:
new = SWITCH(
'my_data'[Position],
"G", "Guard",
"F", "Forward",
"C", "Center",
"None"
)
This particular example creates a new column named new that looks at the value in the Position column of the table named my_data and returns the following value:
- “Guard” if Position is equal to “G”
- “Forward” if Position is equal to “F”
- “Center” if Position is equal to “C”
- “None” if the Position column does not contain any of the previous values
The following example shows how to use this function in practice.
Example: How to Write a Case Statement in Power BI
Suppose we have the following table named my_data in Power BI that contains information about various basketball players:
Suppose we would like to create a new column that contains the values Guard, Forward and Center instead of C, G and F.
To do so, we can click the Table tools tab, then click the New column icon:
Next, we can type the following formula into the formula bar:
new = SWITCH(
'my_data'[Position],
"G", "Guard",
"F", "Forward",
"C", "Center",
"None"
)
This will create a new column named new that contains the values that we specified in the SWITCH function:
Notice that this formula returns the following values in the new column:
- “Guard” if Position is equal to “G”
- “Forward” if Position is equal to “F”
- “Center” if Position is equal to “C”
- “None” if the Position column does not contain any of the previous values
Notice that the last value in the new column returns a value of “None” since we didn’t specify a specific value to return for “Z” in the formula.
Note: You can find the complete documentation for the SWITCH function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: