Tableau Version v10.0+
What are relevant value quick filters?
Relevant value quick filters, or cascading filters as they are often referred, are a wonderful user friendly way to quickly slice and analyze data. The concept is hierarchical in nature. If I select a top level filter all lower level filters should update with the values that are associated. This is very easy to turn on in Tableau with a single click for each filter.
Why is this blog written?
There is a performance impact, however, with relevant value quick filters. For each relevant value filter Tableau has to issue a query for each. In some cases each of these queries could take 1 second to a few seconds, resulting in wait times upwards of 8-10 seconds. The reason these filters can take seconds to render is associated to the complexity of other filters as well as the size of the data source. Example below:
- I am building my dashboard off of a data source that has 100M records. I have 4 filters that are static throughout the workbook (data source filter) and 7 total quick filters that the users can interact with. 4 of those 7 quick filters only show relevant values. The query sent to this 100M record data source for each of these relevant value quick filters would need to select the distinct list of values for each quick filter and only those values that match the 7 other filters (4 data source filters and 3 non-relevant value quick filters).
With Tableau v10, there is a new approach that can be taken to give much better performance with relevant value quick filters. Referencing the above example, our approach would still take into account all of the filters, but the size of the data source would be GREATLY reduced improving the query time significantly.
We will take a 2 data source approach for this solution. Data Source 1 (which we will call Primary) will remain the original 100M record size. Data Source 2 (which we will call Secondary) will be used solely for filtering and will be much smaller; depending on the relationship of the filters, less than 1,000 records. The technology in v10 that allows us to perform this solution is cross data source filtering. The user will select filters from Secondary and these filters will perform 2 functions: 1) to update the relevant values of the other filters and 2) filter the dashboard that is built from Primary.
Below are the steps to follow to accomplish this solution:
1. Start with attached workbook v10 Relevant Value Quick Filters_Start.twbx
2. Open a new sheet and duplicate your data source.
3. Drag into the view the fields that you would like to use as filters. In this case, Category, Sub-Category, and Product Name.
4. Hide All Unused Fields on the data source
5. Create a new extract of the data source
6. Choose aggregate data for visible dimensions and extract
7. Choose show filter for the 3 fields and remove Product Name from the view
8. Add this new sheet to the dashboard
9. Ensure the 3 filters are on the dashboard
10. Set these 3 filters to Apply to Worksheets > All Using Related Data Sources
11. Because we created this Filter Extract from the same data source as the Primary and the field names are the same, all relationships are mapped automatically. In the event that your field names are different in the Secondary and Primary sources, simply navigate to the Data Menu > Edit Relationships. From there you would choose Custom and map the fields from Primary and Secondary as needed.
12. At this point changing the filters should update all sheets on the dashboard. Next we will need to update the necessary quick filters to use only relevant values. In this case Sub-Category and Product Name.
13. Enjoy your dashboard that should be performing much faster than before!