How to filter rows based on whether a column contains a parameter value, and set a default control selection using that parameter

Q&A
, ,
1

Hello QuickSight Community,

I have a question about filtering dataset rows using a parameter value.

I would like to pass a string value via a QuickSight parameter (for example, from a URL or embed parameters), and then filter the dataset so that only rows where a specific column contains that parameter value are shown.

Example:

  • Parameter name: DefaultShopID (String)
  • Parameter value: “4860”
  • Dataset column: shop_name_ids (String)
  • Column values example:
    • “TestShop(4567)”
    • “TestShop2(4678)”
    • “AnotherShop(4860)”

Expected behavior

  1. Filtering
    Only rows where shop_name_ids contains "4860" should be shown.
    (Equivalent to SQL: WHERE shop_ids LIKE '%' || :DefaultShopID || '%')

  2. Filter control default value
    I want to display shop_name_ids as a filter control (dropdown or list),
    and have the control default to the value that contains the parameter, e.g.:

    • When DefaultShopID = "4860"
    • The control should automatically select “AnotherShop(4860)” (because it contains 4860)

Questions

  1. Is it possible in QuickSight to filter rows based on “column contains parameter value”?
  2. Can a filter control automatically select the matching value based on a parameter?
  3. If not directly supported, what is the recommended workaround (calculated field, dataset prep, etc.)?

Thank you in advance for your help.

1 Like
2

Hello @candemina, is the parameter you are using to control the filter a single value parameter or a multi-value parameter?

If it is single, this should be pretty straight-forward, but I can also tell you how to manage select all. We can write a calculated field to make the filtering really simple.

*To check for select all in your parameter value, you can set the default value to ALL_VALUES then in the calculation, we will check if it is NULL

ShopIDFilter =


ifelse(isNull(${DefaultShopID}), "y",

isNotNull(${DefaultShopID}) AND contains({shop_name_ids}, ${DefaultShopID}), "y",

"n")

Then, add the ShopIDFilter field as a filter to your visual, and require the value to return “y”. That should manage the scenario you are looking for! I’ll include a link to the documentation for the contains function as well: contains - Amazon Quick Suite

8 Likes