Filtering Filters?

I need to build a pivot table that can compare my data against other similar benchmark data. Also using row-level security.

If I had this data-set and my resolved tenant was ‘tenant-1’, my row-level data-set would all nulls and tenant-1 (rows 1, 2, 5, 6, 7, 8, 9 into the dashboard view).

I want to filter on the benchmark-tag; default would be my-org. Here the dashboard shows:
1 - cat-foo, my-org, 2
2 - cat-bar, my-org, 3

Problem: I only want to show benchmarks associated with tenant-1, not the full set of benchmarks. Let’s say of rows 5-9 the tenant only has tag-aaa and tag-bbb for rows 5-8. I don’t want to show tag-ccc in the filter droplist.

I’d want to see in my droplist only [ my-org, tag-aaa, tag-bbb]. I’d force to include my-org always as an hidden filter. So if I want to compare with tag-aaa I want to see:
1 - cat-foo, my-org, 2
5 - cat-foo, tag-aaa, 5
2 - cat-bar, my-org, 3
7 - cat-bar, tag-aaa, 7

row num - tenant, category, benchmark-tag, count
1 - tenant-1, cat-foo, my-org, 2
2 - tenant-1, cat-bar, my-org, 3
3 - tenant-2, cat-foo, my-org, 2
4 - tenant-2, cat-xxx, my-org, 30
5 - null, cat-foo, tag-aaa, 5
6 - null, cat-foo, tag-bbb, 4
7 - null, cat-bar, tag-aaa, 7
8 - null, cat-bar, tag-bbb, 9
9 - null, cat-xxx, tag-ccc, 100

I could add tenant-1 tags to the dataset by joining those in. However for each tag a tenant had it would add extra rows. Now I have a column with (tag-aaa, tag-bbb, null) I could use.
1 - tenant-1, cat-foo, tag-aaa, my-org, 2
1b - tenant-1, cat-foo, tag-bbb, my-org, 2
2 - tenant-1, cat-bar, tag-aaa, my-org, 3
2b - tenant-1, cat-bar, tag-bbb, my-org, 3

5 - null, cat-foo, null, tag-aaa, 5
6 - null, cat-foo, null, tag-bbb, 4
7 - null, cat-bar, null, tag-aaa, 7
8 - null, cat-bar, null, tag-bbb, 9
9 - null, cat-xxx, null, tag-ccc, 100

And that impacts 5, 6, 7, 8, 9 where I’d have to put null. I’d have to somehow exclude ‘null’ from the filter selection list but not the data. Also I’d want to use the value set from one column to filter different column.

Have you looked into setting dynamic default parameters?

You would need to make another dataset with your logic but I think it will solve your issue. Then you can filter / change values based on this parameter.

Let me know if that helps at all

Hi @sirwin007. Did Max’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

Looks like that would work, but I haven’t tried it.

Good to know I can do this.