Filter control using more than one field value

[anneshie]

It is easy to create a filter control and select multiple values when there is only one true value, but what if there are more than one true values per row?

See attached excel example where multiple rows contain more than one value.

In Quick sight I would like to create a control (possibly with parameters) so that the user can select, in this example case, a flavor such as “Cherry” and show all rows/ fields where “Cherry” is true even if combined with other flavors. Also the Option to select more than one flavor at a time in the same view.

I did create a control using the field of “Flavor” however when more than one flavor is true the count is off and Quicksight doesn’t recognize it.

In the excel example I have 85 items, but when all Flavor pumps are counted there are 192 flavor pumps. Only 85 items but some of the items have more than one flavor. I need my filter control to take this into consideration.

Goal is to show a report where 1 (or more) flavor is true and have a report to show all associated items by flavor.

[kanakatv]

Try creating a multi-valued control (have to manually define values as your flavor pumps are not defined in a single column but rather spread across multiple columns. Do note that every flavor is a dimension and pump counts are measures. So if you are trying to aggregate (count or sum) multiple flavors at once, the math will be off and wouldn’t make sense. Example: sum of Sales in cities like Bangalore, Delhi, Hyderabad etc makes sense individually but not in a combination of 1 or more cities.
Hope this helps and let us know if any further questions!
Thanks!

[anneshie]

Thanks- can you please send a screen shot of how to set up the calculated field? I have created one along with the new control, but for some reason the widget is unresponsive to my selections. Thanks!

[kanakatv]

Here are the detailed steps to create a Parameter-Control-Filter for your dataset/visuals (calculated field not required here) –

1. Use the + in Parameters to ‘Add Parameter’, give it a name and click on create.
   
   
2. Choose the Control option to ‘Create a new control for a filter’
   
3. Give it a name (say ‘Flavor’), change the style to ‘List’ and manually add all values here.
   
   
   
   Controls on ‘Flavor’ is now added to your sheet and it will appear on the top of the sheet but it is not functional yet.
   
4. Click on the visual chart. Select Filters and add a new filter on ‘All Flavors in Items’
   
   
5. Click on the newly added filter/edit filter and select filter type = Custom filter
   
   
6. Change Filter condition to ‘Contains’. This is the most important step for partial keyword search (i.e., individual flavor match in this case).
   
   
7. Check mark the ‘Use Parameters’ and choose Yes/no to apply filter to all visuals.
   
   
8. Select ‘Flavor’ in the ‘select a parameter’ and click apply. You have now successfully linked your parameter/control to your filter.
   
   
9. Your parameter-control-filter is now set for the field ‘All Flavors in items’ for partial searches (say ‘banana’ or ‘cherry’) and it will list all entries that include these. You can select the flavor of your choice and see the results in the visuals.
   
   

Hope this is helpful. Let us know if any other questions.
Thanks!

[anneshie]

Thank you for the clear and excellent guide, much appreciated. I actually have tried this but I need it to be related to a single visual and it does not seem to work when I change it to single visual only.

[DylanM]

Hello @anneshie, can you provide some more information about the error? From what I can tell, there isn’t a reason that the filter would work for the entire sheet but not for a single visual. What is happening when you set the filter to a single visual? Are you utilizing contains in the filter condition dropdown?

[DylanM]

Hello @anneshie, since we have not heard back from you with any follow-up information, I will close out this topic. If you still need assistance, please post a new question in the community and link to this topic to provide relevant information. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!