How to search a string in a list using ifelse in aws QUICKSIGHT

[Aniruddha_Sinha]

I have a parameter “pDimension” which is of String datatype and ‘Multi values’.
The values (Specific values) it contains are
dim1
dim2
dim3

I have a control (Control options: list) in my report showing all these values listed -
dim1
dim2
dim3
I need to create a calculated field in one dataset used in my report where i need to write a ifelse to search ‘dim1’ in ${pDimension}

How to do that ?
I tried locate() function or in() function, it gives error telling i am searching string in list, so datatype mismatch error.
Please can someone suggest.
Many Thanks
Ani

[Thomas]

Hi @Aniruddha_Sinha,
You can use a formula of the following style in the calculated field to achieve what you’ve described (simply replace the static “true” and “false” values with your desired logic):

ifelse(in("dim1",${pDimension}),"true","false")

Furthermore, I’d recommend to hide the Select all option from the control as it can cause errors in some formulas and expressions. You can do that in the control options when editing the control. The following screenshot shows how the result would look like:

I hope my solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking his answer as a “Solution”.

[Sanjeeb2022]

Thank you @Thomas for sharing this solution, very detailed.

Regards - San

[Aniruddha_Sinha]

Thanks a lot @Thomas It has worked. I have used this concept in self serve capabilities facilitating report users to select their own dimension columns and measure columns!
Wonderfully working !
Many Thanks
Ani

[Kristin]

Awesome to hear this @Aniruddha_Sinha! Thanks so much for sharing this solution, @Thomas!