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?
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)?
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.
I have a similar Problem and do not get it to work. The join operation betweens these datasets is way too big and creates way too many rows and thus performance issues for the Snowflake DB.
Thus I need the filtering by controls to work in accordance to your example to work.
1. Dataset: Product_KPI_Dataset : Customer, Brand, ASIN , …
This Dataset holds the KPI Data for all products.
2. Dataset: Product_Groups_Dataset : Product Group, ASIN, Customer, Brand
Every product groups holds a number of ASINs for the customer and the Brand.
Mission: The 1. Product_KPI_Dataset should be filtered by the Product Group selected by Product_Groups_Dataset. Only products (ASINs) of that product group shall be displayed in the Product_KPI table.
Steps taken: In accordance to the provided instructions
Create filters using the 5th (here 1st Product_Groups_Dataset) dataset (Product Group, PG_ASIN, Customer, Brand) for a visual. You can later hide it using free-form layout and conditional rules.
Filters: Customer, Brand, Product Group, PG_ASIN
Customer filtered by Product Group, PG_ASIN selected
Brand filtered by Product_Group, PG_ASIN, Customer selected
Create a visual from 1st dataset and 2nd dataset [Use a table visual, easier for comparison]
done: Product KPI Table
Add the common key (Account ) to filter for both visuals . Filter properties > Applied to > All applicable visuals.
done: Customer, Brand, PG_ASIN
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.
Problems:
The Product Groups does not filter the Product KPI Table.
Selecting single ASINs from PG_ASINS does the job: it filters the table, but “Select All” always gives the full unfiltered product list from Product KPI Table, not filtered by Product Group.
=> Issue 1 is way more Important then issue 2. We need these filters to work. Please help us and let me know, where the mistake is
=> here is the example Dashboard: Dashboard