In this blog we will show how to write case statements in Power BI, and how they differ from writing case statements in Tableau.
How do you write a CASE statement in Tableau?
Since the syntax in Tableau (VizQL) is very similar to SQL syntax writing a CASE statement in Tableau is identical.
For example, if we wanted to group our regions seen here into distinct region groups our case statement could look like this:
CASE [Region] WHEN "East" THEN "Group A" WHEN "South" THEN "Group A" WHEN "Central" THEN "Group B" ELSE "Group C" END
How Do You Write a Case Statement in Power Bi?
Writing a CASE statement in Power BI is different due to the differences of SQL and DAX syntax. Sadly, there isn’t a CASE function in DAX but there is a function that acts almost identically. For this example, we will use the SWITCH function to get the desired results.
The SWITCH function uses this basic syntax:
New Column = SWITCH( 'data_table'[condition], " condition1", "result1", " condition2", "result2", … "default result" )
When we write this in Power BI the steps should go like this:
- Start by clicking on “New column:”
- Type your SWITCH function in the calculation window. It should look like this:
Region Group = SWITCH(Regions[Region], "East","Group A", "West","Group A", "Central","Group B", "Group C" )
You can read more about the SWITCH function here.
Additionally, you can use an IF statement to get a result similar to what you would achieve with a CASE statement in DAX. However, due to the difference between the way DAX calculates IF() the IF statement to be nested and it has the potential to get messy. For example:
Region Group = IF ( Regions[Region] = “East”, "Group A", IF ( Regions[Region] = “West”, "Group A", IF ( Regions[Region] = “Central”, "Group B", IF ( Regions[Region] = “South”, "Group C", ) ) )
Stay tuned for more Power BI tips for Tableau Developers!
