This write-up is about using multi-select parameters in calculated fields of a workbook. If you are interested in using multi-select parameter in a hand-written SQL - then click here.
In workbooks, when you create a parameter and enable a multi-select, selected values are returned to you as an array of elements. This behavior is different, from the way multi-select parameters worked in Dashboards.
I have an example of using multi-select parameters in a workbook, where [Country-Param-p12] is a multi-select parameter. Let’s start by selecting 4 countries from the listbox:
Now, if I add a calculated field to the table, that displays the parameter:
[Country Param Array] : [Country-Param-p12]
it shows that this is an Array with 4 values:
OPTION 1 - Array-based filtering:
Now we are going to build a filter, based on this parameter and the Snowflake function ARRAY_CONTAINS(), wrapped into a Sigma pass-through function CallLogical(), like this:
[Country Param Filter - Array Functions]:
CallLogical(“array_contains”, [Country Array Element], [Country Param Array])
The only other thing we had to do here, was to take our string field [Country] and convert it into the Array variant data type :
[Country Array Element]: CallVariant(“to_variant”, [Country])
Now, let’s apply a filter on [Country Param Filter - Array Functions], filtering out Null of False values
This works fine, except it will return no data unless you select at least one country, which may be what you want. But, often you want to return all data, when the parameter is empty.
Here is how you would improve the formula to support that use case:
Create a new field, that will calculate the number of selected items in the listbox:
[Country Param - Array Size]: CallNumber(“array_size”, [Country Param Array])
Then modify the filter formula [Country Param Filter - Array Functions]:
If([Country Param - Array Size] = 0, True, CallLogical(“array_contains”, [Country Array Element], [Country Param Array]))
Now, if no Country is selected, the above formula returns True, and all rows of data are displayed.
CallLogical() is a Sigma pass-through function, that basically calls any Snowflake function, that return a True/False value.
OPTION 2 - text-based filtering using Contains():
There is also another (legacy) way to accomplish the same ( this is similar to the approach, we have taken with handling multi-select parameters in dashboards). You can convert the multiple values, coming from the multi-select listbox as JSONs, into a text string, then use a simple Contains() formula:
[Country Param Txt]= Text([Country-Param-p12])
[Country Param Filter - Contains]: Contains([Country Param Txt], ‘“’ & [Country] & ‘“’)
Then, add a filter on [Country Param Filter - Contains], filtering out null of False values