How to filter multiple datasets using a single filter originating from a separate dataset in Analysis?

I have four different datasets in Analysis, each containing an “account” column that needs to be filtered by a single filter originating from a fifth dataset. This fifth dataset contains all possible accounts, along with additional information such as country, segment, size, etc. Using this separate dataset as the source for the filter is essential to ensure that all accounts are included and that I can use the additional account information in other filters. If I create the filter from one of the four datasets, it will only include accounts related to that particular dataset (because the list of accounts for each dataset is different, only the fifth dataset contains all of them). How do I create a “universal filter” that will filter all the datasets, based on “account” columns from a fifth dataset?

I’ve attempted to join the fifth dataset with the other four, but the resulting filter didn’t function as expected. In addition, performing a JOIN operation can cause a significant decrease in performance since the resulting dataset has many columns and potentially millions of rows.

Furthermore, when attempting to create a filter based on the fifth dataset, it requires me to create at least one visual with it, which is not ideal for my purposes. I would like to create a “universal filter” that can filter all four datasets, based on a single “account” column, without the need to create a visual.

Ultimately, my goal is to use my comprehensive accounts dataset, which includes all the essential account information, to create filters that can filter other datasets based on the “account” column, which is also present in the other datasets.

Can anyone guide me on how to achieve this in Analysis?

Hi @Pedro_Andrade ,

You can use parameters : Using QuickSight parameters and controls to drive interactivity in your dashboards | AWS Big Data Blog ( Search for : Use case 2: filtering across multiple data sets )

Kind regards,
Koushik

3 Likes

Thank you, @Koushik_Muthanna! Although this is a great solution, it does not solve my problem entirely.

This is perfect to have a single “account” filter across all datasets. But the other problem is that, on this fifth dataset, I have other columns for each account, such as segment, country… So I also want to have a “country” filter, for example, to be used across all datasets. The other four datasets do not have these columns, but they can be linked through the accounts columns between each dataset and the fifth one. There is a way to create a parameter that does that, without doing any JOIN (what would not be performative)?

Hi @Pedro_Andrade ,

Test the following
1/Create filters using the 5th dataset ( Segment , Country , Account ) for a visual. You can later hide it using free-form layout and conditional rules.
2/Create a cascading control Show relevant values only [Adding filter controls to analysis sheets - Amazon QuickSight]
3/Create a visual from 1st dataset and 2nd dataset [Use a table visual, easier for comparison]
4/Add the common key (Account ) to filter for both visuals . Filter properties > Applied to > All applicable visuals.
5/ Select a country and segment . It should show only relevant values for account. The same account should also be filtered in 1st and 2nd dataset if available.

If the above works then you do not require a join , else you would have join the dimension table to each of those individual datasets.

Kind regards,
Koushik

1 Like