Description
Compares one expression or block against a list of conditions and results, and returns the result of the first matching condition. If there is no match, returns an optional default result or blank if there is no default.
Syntax
SWITCH(Expression, Value1, Result1 [, ValueN, ResultN] [, Default])
Argument | Type | Dimensions | Description |
|---|---|---|---|
Expression | Any | Any | Can be a List Property, a Metric, a List or a Formula returning a result. Each value of the Expression will be compared to the values provided in the Switch. |
Value1 | Any | No Dimension | At least one pair of {value, result} is required. Must be a scalar value and of the same data type as Expression. A scalar in this context is a singular (not dimensioned) value. |
Result1 | Any | Any | The result when a value of Expression matches Value1. |
ValueN (optional) | Same as Value1 | No Dimension | Same as Value1: you can provide any number of pairs, so you can have |
ResultN (required for each ValueN) | Same as Result1 | Any | Same as Result1. |
Default (optional) | Same as Result1 | Subset of Result1 | Optional argument that defines the output when there is no match for any Value, including blank values from Expression. |
Return type
Same data type as the Result values.
Example
Note that to improve readability, especially with multiple {value, result} pairs, it is recommended to put each one on a separate line.
Case | Return Type | Result |
|---|---|---|
|
|  BR has blank and non-matching values, so the results are blank (no default value) (click to enlarge) |
|
|  BR has blank and non-matching values, so the result has the default value (click to enlarge) |
|
|  Similar example with a Metric used as Default (click to enlarge) |
|
|  Switch on a Dimension (click to enlarge) |
Tips
When using the Switch function with Dimension items, a common mistake is to specify values as strings instead of “references”. The main risk with strings is that it’s not future proof, e.g. renaming the value in the source Dimension could silently break some Switch cases:
❌ Not recommended | ✅ Do this instead |
|---|---|
 click to enlarge If you rename “NA” to “North America” in the Region dimension, the Switch will still have “NA” and won’t match that value anymore. |  click to enlarge Notice how |
Note that this is true not only for the Switch, and is a best practice to follow as much as possible.
Excel equivalent: SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
See also: IF
More of a hands-on learner?
Talk to your Customer Success Manager about downloading the Functions and Modifiers in Pigment Application into your workspace. It includes examples of every formula and modifier in Pigment!