Amazon QuickSight – switch

The ‘switch’ function in Amazon QuickSight evaluates a given condition-expression against a series of label-expression pairs and returns the value of the corresponding return-expression for the first matching label-expression. If no label-expression matches the condition-expression, the function returns the value of the default-expression.

Syntax

#Start#
switch(condition-expression, label-1, return-expression-1 [, label-n, return-expression-n ...], 
    default-expression)
#End#

This function takes one argument:

• condition-expression (required): the expression to be evaluated against the label-expression pairs.

• label-1 (required): the first label-expression pair.

• return-expression-1 (required): the value to be returned if the first label-expression matches the condition-expression.

• label-n (optional): additional label-expression pairs.

• return-expression-n (optional): additional return expressions to be returned if the corresponding label-expression matches the condition-expression.

• default-expression (optional): the value to be returned if no label-expression matches the condition-expression. 

Suppose you have a dataset containing sales data for various products, and you want to create a calculated field that assigns a letter grade based on the total sales for each product.

Assume the following dataset:

To create a calculated field that assigns a letter grade based on the total sales for each product, you can use the switch function:

• Click on the “Add calculated field” button in the analysis pane.
• Enter a name for the calculated field, such as “Sales Grade”.
• Enter the following expression for the calculated field:

Example

#Start#
switch({Total Sales},
    0, "F",
    1000, "D",
    3000, "C",
    5000, "B",
    7500, "A",
    "N/A")
#End#

This expression evaluates the {Total Sales} field in the current row against a series of sales thresholds and assigns a corresponding letter grade based on the first matching threshold.

• Click “Create” to create the calculated field.

After creating the calculated field, the dataset would include the following additional column:

In this example, the switch function is used to assign a letter grade to each product based on its total sales. The function can be useful for assigning categorical values based on quantitative data.