The SWITCH function evaluates an expression and returns one result or another based on the value resulting from the evaluation, a value that will be found among a list of options.
SWITCH(
expression,
value, result
[, value, result]…
[, else]
)
SWITCH(
TRUE(),
condition, result
[, condition, result]…
[, else]
)
- expression: Expression to evaluate. It must return a scalar.
- condition: Expression to evaluate. It must return a Boolean.
- value: Constant value with which the result of the expression evaluation will be compared.
- result: Value to be returned by the function if value matches the result of the evaluation of expression or if the condition included in the previous condition argument is satisfied.
- else: Optional argument. Result to return if the result of the evaluation of expression does not match any value or if none of the conditions included in condition are satisfied.
A scalar value from the list of values contained in value or else.
If the else argument is not included and the result of the evaluation of expression does not match any value, SWITCH returns a Blank.
If the TRUE() function is added as the first argument, the value arguments can be replaced by conditional expressions, returning the first result for which the corresponding condition is met (see example below).
The following example evaluates the country to which the sale is associated to generate a custom column that contains the continent:
The values to which the expression evaluation result is compared and the returned values do not have to be of the same type. The following example calculates the transportation cost as a percentage of the sales value, a percentage that varies for each country:
To test the function not with values, but with conditional expressions, suppose we define the measure
Ventas = SUM(FactSales[SalesAmount])
...adding the sales in the current context. If we take the field containing the list of subcategories and this measure to a table, the result is the following:
If we wanted to classify each category according to the total sales figure on a scale from 1 to 3 (indicating it with "$" symbols, for example), we could do so using the SWITCH function as follows:
Clasificación = SWITCH(
TRUE(),
[Ventas]>1000000000, "$$$",
[Ventas]>100000000, "$$",
[Ventas]>0, "$",
BLANK()
)
In this measure, we compare the total sales (in the current context) with a figure, and we return one value or another depending on whether the comparison is fulfilled or not. The result is as follows:
It is noteworthy that the result returned by the function is the first for which the condition is met. Thus, if the measure [Clasificación] were defined as follows (changing the order of the comparisons):
Clasificación = SWITCH(
TRUE(),
[Ventas]>0, "$",
[Ventas]>100000000, "$$",
[Ventas]>1000000000, "$$$",
BLANK()
)
...the result would be the following:
As we can see, as the first condition is met for all the displayed subcategories ([Ventas]> 0), the SWITCH function always returns the result "$", which is not what we are looking for.
