SWITCH

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.

Syntax
Hay dos posibles sintaxis para esta función:

SWITCH(
    expression,
    value, result
    [, value, result]…
    [, else]
)

SWITCH(
    TRUE(),
    condition, result
    [, condition, result]…
    [, else]
)

Parameters
  • 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.
Returned value

A scalar value from the list of values contained in value or else.

Additional Information

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).

Examples

The following example evaluates the country to which the sale is associated to generate a custom column that contains the continent:

SWITCH function. Example of use

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:

SWITCH function. Example of use

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:

SWITCH function. Example of use

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:

SWITCH function. Example of use

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:

SWITCH function. Example of use

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.

Category
Logical
Submitted by admin on Tue, 12/04/2018 - 00:16