Using multivalue parameter with select all in a calculated field

I have a “Name” field in my dataset. I have created a multivalue string parameter “pNames” with blank default value. I have created a drop-down control on top of this parameter. I am using this parameter in a calculated field “nameExists”. I am using the following expression to calculate this field:
ifelse(in({Name}, ${pNames}), 1, 0)
When the user selects multiple control values, these values are stored in “pNames” parameter and are used in the calculation of the “nameExists” calculated field and everything works smoothly.
However, the moment the user selects all values of the control, the calculated field “nameExists” only contains 0.
I have been looking for an explanation in the documentation and somewhere I read that when all values of the control are selected, QuickSight assigns an empty string to the underlying parameter. However, since the parameter “pNames” is of type String List, I can not use functions such as “contains”, “in” or just writing ${pNames}=’ ', or isNull(${pNames}) so that I could add an OR condition to my expression to handle this case.
Have someone found a workaround for this issue ?! Could you please suggest a solution ?!
Thank you in advance.
Best regards,
Hatef

Have you tried saying NULL in ${pNames} or ‘’ in ${pNames}

ifelse(in({client_id[users]}, ${ALLVALUES}) OR in(NULL,${ALLVALUES}), 1, 0)

2 Likes

Thank you @Max for your answer. I checked “NULL” and it works in the case of “Select All” for the parameter. This way I can take the “Select All” case of the control into consideration in my calculated field by doing ifelse(in({Name}, ${pNames}) OR in(NULL, ${pNames}), 1, 0).

However, now I am facing another issue. As I mentioned, the parameter “pNames” is set to be blank by default. Do you know how can I check if the “pNames” parameter is empty (has no values selected by the user) in my calculated field calculation ?!
Is there a similar value like NULL in the case of the “Select All” that I can check for the case of no values ?

So in total, I will check three scenarios in my logic:

  1. If the parameter “pNames” contains specific values select by the user. That would be in({NAME}, ${pNames}).
  2. If the parameter “pNames” contains NULL because the user has chosen “Select All”. That would be the in(NULL, ${pNames}).
  3. This is the case I want to check now. If the user has chosen not to select any values and the parameter “pNames” still contains the default value which is blank. What should be the clause now ?! I checked: in(’ ‘, ${pNames}) and in(’', ${pNames}) so far and it doesn’t seem to work.

I would really appreciate your help.
Thank you so much.
Hatef

@HatAbd9241 I don’t believe there is the ability to check that.

I can mark it as a feature request though. I also don’t think you have the ability to check the length of ${pNames}.

I can mark it as a feature request.

2 Likes

Hi @Max,

Thank you for marking this as a feature request.