Hello team! I have two questions:
I am have a column in my dataset which has multiple comma separated values. For example
Name Tags
A Tag1, Tag2
B Tag2, Tag3
C Tag1, Tag2, Tag3
I want to have a filter for my visual (table) as distinct values of Tags. So the filter should show Tag1, Tag2 and Tag3 and if a user selects Tag1, all matching rows from the dataset that have Tag1 from the ‘Tags’ column should be displayed. Any idea how I can create this?
2. Is there an option to indicate with arrows next to a field in a table visual that sorting is possible? Currently, one can find the sorting option only if one clicks on the relevant column/field
Hello @mehtasal, welcome to the QuickSight Community!
If you are returning multiple tags in a single row value, there won’t be a clean way of filtering those value. If you wanted the filtering capabilities to work better with your data, I would recommend either having a row for each tag that is linked to a name, or you could create a column for each tag and then return true or false for each name to keep them on the same row. That would either need to be done in your SQL query for your dataset or in the data source itself.
The other option I can think of would continue to return a row value that contains “Tag1, Tag2” if Tag1 was selected in your filter. Set your control to be a dropdown and write out all of the possible Tags contained in the field. Then create a calculated field that would use a contains function to check if Tags contains the value returned from the parameter, then return tags else null.
As for your 2nd question, there currently isn’t any more functionality than what you previously stated in regards to sorting for a table. Since this is an added capability you would like to have access to in the table functionality, I have tagged this as a feature request. Thank you!
2 Likes
Hi Dylan, thank you so much for your response. Could you help me with the solution of setting control to be dropdown with all possible tag values and the calculated field bit? I am getting an error if I use contains ({parameter_name}, field-name). If you could pls help with a step by step for this, that would be great!
Thank you again
If my field ‘Marketecture’ has values such as ‘1 - Awareness - Reach more customers 3-Consideration-Drive Traffic’ I would expect the calculated field to return True? But I get 0
Hello @mehtasal, for the control you have 2 options.
- Manually enter all of the values, in that example above that you just posted you could just say “Awareness” if that exists in the tag.
- Create a dataset field that returns all of the possible tags individually (not grouped with commas) on each row and link the control to that dataset field.
Then your ifelse statement would look like this:
ifelse(contains({field_name}, ${parameter_name}), {field_name}, NULL)
This will return your field “Tag1, Tag2” as is unless you change how the dataset returns Tag values, but it will only return the values that contain the name in the parameter.
Then you can filter your visuals using the ifelse calculated field, use a custom filter, and select exclude nulls to avoid filtered values manipulated the data displayed on your visual.
2 Likes
Hi Dylan,
Thank you so much - this seems to work partly but I have a few questions
-
If I filter my visuals using the ifelse calculated field, and set it up as a custom filter, should I set it to the parameter? if yes, i don’t see the option to exclude nulls.
-
This solution allows me to select say, Tag 1, and get all results that have Tag 1; can I use multi-drop down and select Tag 1 and then Tag 2 which will show me results from both?
-
If I want to have an option for ‘Select all’ - should I just add that as one of the values in the control and use an ifelse condition to return all?
-
If I do have nulls in the column, is there anyway to show nulls when say, the default value is ‘Select All’?
Hello @mehtasal -
- If you are building a filter for that calculated field do not set it equal to the parameter, because we are already accounting for the parameter value in our ifelse statement. I will usually set it to a “Does not equal” option and enter an impossible value, then select exclude nulls
- As for the multi-value select dropdown, the calculated field will no longer work with the parameter if it is multiple values. Here is the documentation for using parameters in calculated fields.
- To handle the “Select All” option, I will usually add a filter value for “All” and remove the ability to Select All and use that as one of the options in the ifelse statement to grab all Tag values. I have not found a good way to utilize the default select all option in the calculated field check.
- If you want to include NULLs only on “All”, you won’t want to exclude them on the filter.
I hope that manages all of your expectations!
Hello @mehtasal, just thought of one thing for the final option you mentioned. You could continue to use the filter to Exclude Nulls, but have 2 options for when ‘All’ is selected.
${parameter_name} = 'All' AND isNotNull({field_name}), {field_name},
${parameter_name} = 'All' AND isNull({field_name}), "Null"
This would return a string value for Null, which you could switch out with whatever you want. That way, it would only show those Null values when All is selected and you can leave the filter on the calculated field to avoid displaying NULLs when any other option is selected.
1 Like