Cascading Filter Without a Trigger?

Question & Answer
, , ,
1

“Cascading Filter” might be the wrong approach as it needs a trigger.

My dataset contains many rows that I need to filter out initially for the user, based on an post filter aggregation function. Based on the filter control (e.g., date range), the 2 visuals correctly filter the data, and the post aggregation calculates if the row should be seen. The calculated function is used as a filter without a control behind the scenes.

My issue/question is this - I have another filter control and I need that to be limited based on what’s currently shown. What it shows is the Full list of options before the offscreen filter limits. Seems like it should get a cascading filter - but to what? This isn’t an action either.

2

@sirwin007 ,

Sample data example with the expected output would be more helpful .

If you have 2 filters and you want to show relevant values in the 2nd filter based on a selection in the 1st filter, then you have to use cascading filters.

Are you trying to restrict what data the user should see or just filtering data ?

My dataset contains many rows that I need to filter out initially for the user, based on an post filter aggregation function.

1 Like
3

The dataset contains a full list of items. For row security these are shown to every tenant. Joined in are a set of tenant specific data. Filters are A, B, Date

Col A, Col B, Col C, Date, tenant
A, 1, null, null, global
A, 2, null, null, global
A, 3, null, null, global
A, 4, null, null, global
B, 8, null, null, global
B, 9, null, null, global
C, 1, null, null, global
C, 2, null, null, global
A, 2, 1, Jan, T1
A, 4, 1, Jan, T1
C, 2, 1, Aug, T1

When there is a value for any of Col A - I need to show all that group. When there is no tenant row for a group - I can’t show the group - B in this case can’t be shown.

Expected visual display

A, 1, 0
A, 2, 1
A, 3, 0
A, 4, 1
C, 1, 1
C, 2, 0

Further - if the user changes the date filter I need to alter the view again. So if the date is set to just Aug - I need to show the following:

C, 1, 1
C, 2, 0

I use a calculated field - maxover(sum(Col C), [Col A]) - as display_count.
I added a filter without a control based on ‘display_count > 0’

This works great for the visuals.

My issue is when I hit the A Filter drop list I see:
A,
B,
C

B was never a valid option. The filter is doing a simple select distinct on the dataset.
B is a dependent filter on A however without changing A - B contains:
1,
2,
3,
4,
8,
9

Where 8/9 where never valid options.

I tried to create a calculated field to replace the basic A Column as the Filter.
ifelse({display_count} > 0, Col A, null) - however display_count is an aggregation and Col A isn’t - invalid.

4

Hello @sirwin007 - Since a few months have passed from your original post I am going to archive this question. If you still need assistance with this please post a new question in the QuickSight community and link to this topic. Due to the community focus on responding to questions with 0 replies, this will ensure you are a top priority for help from one of our QuickSight experts. Thank you!