Use Multi-value parameter in Calculated field to find if the combination is present in column

Q&A
,
1

Hello all,

I have 2 parameters.

First parameter is single value parameter - named TagKeyParameter
Second parameter is multiple values parameter - named tagvalue2

To each of these parameter I’ve created control
For TagKeyParameter I created dropdown control linked with dataset field
For tagvalue I created dropdown - multiselect control linked also with dataset filed and to show relevant values based on selected TagKey

Now it is what I would like to achieve:

I need to create calculated field which show me 0 or 1(yes/no) value depends on logic.
Logic I would like to have there is:

I need to check if combination of TagKey and Tagvalue is present in tags field. So if tags contain this combination.

For example:
I chose Environment in TagKey control (value for TagKeyParameter)
I chose PRD and DEV in tagvalue control ( value for tagvalue2 Parameter)

I need to check if tags column contains Environment": “PRD” OR Environment": “DEV”

How can I achieve it?

2

Hi @Polo87

I am assuming your tags column has data in JSON form. In your dataset you can create a field to store the Value part using parseJson

parseJson - Amazon QuickSight.

Assuming tagValues is a column from the dataset that used the parseJson extracted value part , you can try the following approach to identify if your combinations exist in the dataset. For each Key you have a separate block. I guess even that can be done using the IN clause if you experiment a bit

ifelse(${TagKeyParameter} = “Environment”,
ifelse(IN({tagValues}, ${targetvalue2}), 1, 0),
0)

3

@Giridhar.Prabhu
Thanks for answer.
Few clarifications :slight_smile:

  1. tags column is in json format - that is correct
    2)tagValues is a column in different dataset where I have flattened json already into rows with row for each tag

parseJson coulnd’t be used in anlysis as I am using SPICE. I could do this on Prep data level in dataset however in this solution I need to specify tagkey inside calculated field.

I need to have it dynamic based on selection in controls for TagKeyParameter and tagvalue2.

So if I selected in control Environment TagKey and PRD and DEV in tagvalues control I need to check if string like:
Environment: PRD
or
Environment: DEV

is present in tags column :slight_smile:

4

Mayb there is a way to use mutlivalue parameters in filter to achieve this scenario?

5

Hi @Polo87,
It’s been awhile since last communication on this thread, were you able to find a work around or are you still working on this?
From my understanding the the situation, you may need to handle this in various steps.
Create a filter on your visual that limits the results to your ‘TagKeyParameter’.

Then, create a parameter with custom values that equal your tags (like ‘DEV’, ‘PRD’, etc.).

Last, create an ifelse statement using contains to map each of your values to one of those custom values created for your TagKeyParameter.

Let us know if this could work for your case or if you have any additional questions.

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

6

Hi @Polo87,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!