Calculated field for the “Select All” with QuickSight filter control

Question & Answer
,
1

I need help understanding how to test a parameter value is selected when the “Select All” is selected on a QuickSight control. I can properly handle when selecting no values through less than all values, just not the all values case.

Background

In a calculated field, the in - Amazon QuickSight returns zero instead of an expected non-zero (for true) when ‘Select All’ is chosen on the control using the parameter’s default or custom value for all values (for example the default and suggested “ALL_VALUES”) for Setting up parameters in Amazon QuickSight - Amazon QuickSight

For example: Assuming that my_parameter is the name for a parameter with a control filter,in("A", ${my_parameter}) works only if “A” is selected in a multi-select control as long as the “Select All” is not (even though “A” is still technically selected). This is most problematic when a users dataset only has one value.

I would expect this calculated field in the analysis to be true when “Select All” is used:

in(“ALL_VALUES”, ${my_parameter})

1 Like
2

Hi @Scott_Schreckengaust

Have you tried using NULL?

3

Putting a custom filter type where {my_field} equals the parameter ${pname} with a calculated field in({my_field}, ${my_parameter}) OR in(NULL, ${my_parameter}) allows logic to determine if the record is selected in the control when that control is linked to the dataset field {my_field}.

Currently, NULL is the value currently no matter what the parameter’s Set reserved value is set to whether “Recommended value”, “Null”, or “Custom value”.

Although,in(NULL, ${my_parameter}) detects when the ‘Select all’ is chosen, it detects when the field value is NULL so adding a filter to exclude when {name} equals NULL is helpful. Also, the “Null” selection for the Set reserved value is the most appropriate option because it will exclude the “NULL” value from the control drop down.

1 Like