Configuring Dynamic Filters Using Parameters in Pivot Tables

Question & Answer
, , ,
1

Hello,

I need your assistance in setting up multiple filters using parameters that control a pivot table. These parameters are based on dynamic defaults, pulling elements directly from the database. I’ve created three parameters to display different elements in a dynamic table, and I’d like to implement the following conditions:

  1. Exclusive Selection per Filter: If the first filter selects case01, this option should no longer be available in the other two filters.
  2. Default Value Assignment: Since the parameter values are user-defined and sourced from the database, I’d like to set a default such as “first in list” or “second in list.”
  3. Hide “Select All” Option: Currently, it seems this option cannot be hidden due to the lack of a default setting for the filters.

Thank you in advance for your help!

image
image1625×792 100 KB

2

hi @GeDex

  1. Exclusive Selection per Filter , I can only think of is to use the cascading filter, for these 3 filter controls. Adding filter controls to analysis sheets - Amazon QuickSight
  2. Default Value Assignment, you can configure the default value when creating the parameter
  3. Hide “Select All” Option , yes it requires to set default value.

kind reagrds
Wakana

3

Hi @Wakana

Thank you for your quick response.

The cascading filter will not work because the three filters are related to the same column in the database. This column is dynamically linked and can contain one or more cases. If I use a cascading filter with the second filter (related to the first), the second filter will only show what is selected in the first filter.

Do you have any other ideas?

Thank you for your help.

GeDex

1 Like
4

Hello @GeDex, it seems like you are going to need to implement a work-around solution to manage this issue. The functionality you are looking for in the dropdowns is not going to work as expected because the cascading filter functionality has not been built to manage dropdowns for the same field.

As for managing the Select All functionality, you can include something like ALL_VALUES in the static default value so that you can remove the Select All option from the dropdown. This will be overridden by the dynamic default value that you have set, but it will also allow you to use all of the parameter functionalities.

Now for the work-around, you may need to implement the filters with a table visual rather than a dropdown. Here is the example I created:
Configuring Dynamic Filters Using Parameters in Pivot Tables

Single table visual displaying the options. I created 3 parameters P1, P2, and P3. These parameters will filter the table to exclude them as they are selected. I have 3 navigation actions to set each product with clicks on the table. Then, I included a 4th action to set them all back to “None” which is what I made their default value.

Then I created a calculated field to display them in the pivot table. If the product name matches the parameter value, it is returned. If all of the parameters are showing the default value of None, then it will return a string like “No Product Selected”. Otherwise, it will return NULL. Then I can applied a filter to the table to exclude NULL values to avoid showing rows without any data.

I know this is not perfect, but this will be the best way to accomplish your goal. Let me know if you have any questions!