Switch Dax function
Switch Dax function is a DAX (Data Analysis Expressions) function that returns one of multiple results based on the value of an expression. The Switch function corresponds to multiple nested “if” statements.
SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
In the Switch/else function, the “expression” part is the statement for which the result will be returned based on its value. “value1”, “value2”, etc., are the values corresponding to results for the “expression” value. “result1”, “result2”, etc., are the values of the results corresponding to the “expression” value. The “default_result” part is the result that will be returned if the “expression” value doesn’t match any of the specified “value” conditions.
The Switch/else function is a powerful DAX function that can be used to return one of multiple results based on the value of an expression.
Let’s create an example abaut using “SWITCH” the Northwind Database:
Switch CountryName =
SWITCH (
Orders[ShipCountry],
“United States”, “USA”,
“Canada”, “CAN”,
“United Kingdom”, “GBR”,
“Germany”, “GER”,
“France”, “FRA”,
“Italy”, “ITA”,
“Australia”, “AUS”,
“Japan”, “JPN”,
“China”, “CHN”,
“India”, “IND”,
“Brazil”, “BRA”,
“Russia”, “RUS”,
“Turkey”, “TUR”,
“Mexico”, “MEX”,
BLANK() — If no match
)
We’ve created a new column and displayed the country names from the Orders[ShipCountry] column in the newly opened column using the country codes we’ve defined. Similarly, you can experiment with various combinations using records from your database. For instance, if gender information for individuals comes as “F” and “M” in the database, you can make them appear as “Female” and “Male.” You can adapt the above paragraph to your specific context by exploring different data combinations.
You can see another example below, like this.
values =
SWITCH(
TRUE(),
SELECTEDVALUE(‘Table'[Item]) = “Average”, [avg],
SELECTEDVALUE(‘Table'[Item]) = “Subtotal”, [subtotal],
TRUE(), [data connection]
)
According to the code above:
- If the selected value of the ‘Item’ column belonging to the ‘Table’ is “Average,” it assigns the value [avg] to the ‘values’ variable.
- If the selected value of the ‘Item’ column belonging to the ‘Table’ is “Subtotal,” it assigns the value [subtotal] to the ‘values’ variable.
- If the above conditions are not met, it assigns the value [data connection] to the ‘values’ variable.
AHMET GÜNOĞLU
ERP Consultant and Business Intelligence Expert