Adding multiple conditional expressions with SWITCH


You can use the SWITCH function to create several different conditional expressions as an alternative to nesting IF THEN ELSE functions. This article describes the different components and provides a use case for the SWITCH function. For information on other types of functions, see Formula writing resources.

The SWITCH function is written as below:

SWITCH testedElement { CASE value1: returnValue CASE value2: returnValue DEFAULT: defaultReturnValue }

SWITCH functions test if the case values exist within the tested element. Your tested element can be an attribute, measure, or calculation. If your case value exists within the tested element, then the return value is displayed. If it does not, your default value is displayed. If your case value does not exist in the tested element and there is no default value, "NULL" is returned.

SWITCH is frequently used to insert measures such as targets and budgets into datasets. They can then be used in other calculations (performance in N and %), or in comparison visuals such as KPI, bullet, and gauge charts.

The example below uses the SWITCH function to show revenue targets for each product category. You can duplicate this example using any attribute and target number.

To insert numbers into your data 

  1. In the Calculation engine menu, select Standard calculated measure.
  2. Name your calculated measure. This example uses Target Revenue.
  3. Click the +Add button beneath Functions.
  4. Select the SWITCH function.

  5. Click inside of the _tested_element parentheses.
  6. Select the attribute containing the values you would like to test from the Select a field drop-down list or type in the attribute name. This example uses Category.
    Note: If you manually type in the attribute, you must either select BIME's auto-complete suggestion or replicate the exact format. Calculations will not process incorrect formatting.
  7. In CASE _value1, enter the value you're testing for.
  8. In _return_value, enter the result returned if the value is true. This example uses the value Accessories to test and the target revenue as the returned result.
  9. Enter your other cases. If you have more than two cases, you will need to manually type out additional CASE expressions. The formula for this example looks like the image below.

  10. Click Save.
  11. Click the + button on measures to add your calculated measure to your query.
  12. If your tested element is an attribute, click the + button on any attribute location and select your tested element.

This example places Target Revenue on a trend line to easily track if a category's revenue is meeting its target. For more information on adding measures to trend lines, see Adding measures to a trend line.

Have more questions? Submit a request


Please sign in to leave a comment.
Powered by Zendesk