Table of Contents
A Case Statement in Power BI is a conditional logic statement that allows users to define a specific action or outcome based on predefined conditions. This statement is commonly used in the Power BI query editor to manipulate data and create custom calculations. An example of a Case Statement in Power BI would be using the IF function to categorize sales data into different regions based on their corresponding country codes. This allows for efficient data analysis and visualization in Power BI reports and dashboards.
Write a Case Statement in Power BI (With Example)
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: