How do I create a filter using just one field where the drop down list shows grouped items from the field in Amazon Quicksight?

Question

I need to make regional groups from a 'Company' field in Quicksight. How do you create a filter that will show grouped companies? For example Region 1, Region 2, Region 3, etc. Each of these groups when chosen in the filter will need to show a specific list of companies from the one field 'Company' based on the Region chosen.

I've tried creating separate parameters (Region 1, Region 2, etc.) with the appropriate companies under each one but I could not figure out how to use those in a filter. In short I need to group companies together so the groups can be chosen from a dropdown filter.

Answer 1

I was able to find the answer to my question from another site. I wanted to share.

I had to use the locate function to create my groups in a calculated field. Syntax for Calculated field:

locate(expression, substring, start)

Looked something like this:

ifelse (
locate('Hotel by Marriott',{Hotel Name}) > 0, 'Marriott',

 locate('Homewood Suites Hotel A, Homewood Suites Hotel B ',{Hotel Name}) > 0,
'Homewood Suites',

   
 locate('Home 2 Suites Hotel A,Home2 Suites Hotel B',{Hotel Name}) > 0,
'Home2 Suites',



'Other Brands'

)

Expression is the actual Name in the specific field called Hotel Name list all values for that group in single quotes separated by commas. Substring is the Field reference (Hotel Name) Start is the Group Name ('Marriott') you want to refer to it by.

Then create a parameter as String with Multiple Values. Save and create a control with Multiple Static values which is your list of Group Names as written in the calculated field.