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 Value2, Result2, Value3, Result3... |
| 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 |
SWITCH('Status', 0, "Inactive", 1, "Active") | Text |  (click to enlarge)
|
|
|
|  (click to enlarge)
|
SWITCH('Code', "A", 1, "B", 2, 'Code Default') | Number |  (click to enlarge)
|
SWITCH(Region, Region."EU", "Europe", Region."NA", "North America", Region."LATAM", "Latin America") | Text |  (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 |
|---|---|
 |  Region.Name became just Region. Now if we rename any value in the Region dimension, it will update the value in the Switch automatically as it’s referencing the dimension item, not the literal string. |
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!
