Filtering across blended data sources in Tableau can be a bit tricky - especially when you want to filter by a field that isn't contained in your secondary data source. I recently came up against this issue and found a neat solution to my problem and thought I'd share.
What was the situation?
I had created a quadrant chart showing how each customer was performing in two metrics and had created a calculated field to colour each of the quadrants by.
I then wanted to click on a customer, and filter a separate sheet on the dashboard to all of the customers in this quadrant. Normally this would be achievable by filtering on specific fields, but unfortunately, the data source feeding the second sheet came from a secondary data source. The two data sources were linked on customer, but I couldn't filter by the quadrant.
What was the solution?
The solution I found to my problem was to use a set action.
By creating a set from my quadrant field - and a calculated field to contain only the customers that are in that selected quadrant - I could change the link between the data sources to be between this 'Selected customers' field in the primary data source, and the original customer field in the secondary source.
How to set this up?
I will walk you through this using the Sample Superstore data set included with Tableau. We will be linking the 'Sales' sheet to the 'Returns' sheet so you'll want to get these both added as data sources before continuing if you're planning on following along.
I am using a field I created for the quadrant chart, which you can recreate by copying this formula, or you can just use one of the existing fields in Tableau, such as Segment or category.
Step 1 - create a set from the field you want to filter by
The first thing to do is create a set from the field you want to filter by. Give this a sensible name and select one of the values from that field - this will help going forward.
Step 2 - create a calculated field containing all values in the set
Now we need to create a calculated field that contains all the values in the set. This will be the field we use to link to our secondary data source.
The field I am linking on is 'Order ID', so I have called my field 'Selected orders' and written the calculation below. This returns values for all Order ID's that correspond to the selected quadrant.
Step 3 - link the two data sources
To make changes to the relationship between blended data sources, click on the 'Data' drop-down and select 'Edit relationships'.
You may find that there is an already existing relationship between your primary and secondary data sources - this often happens if the fields have the same name. If so you can edit the relationship, if not simply create a new one.
The new relationship should be between the 'Selected orders' field for the primary data source and the original 'Order ID' field on the secondary data source.
Step 4 - set up the dashboard action
Once this relationship is set up and both sheets are on your dashboard, you can configure the dashboard set action. This is the final step and will result in filtering between the blended data sources at your desired level of granularity.
When on the dashboard, click on the 'Dashboard' drop-down and select 'Actions'. Click on the 'Add Action' button and choose 'Change Set Values'.
Give the action a sensible name and configure the action. Select the source sheet (in my case the quadrant chart), make sure the 'Run action on' is configured the way you want it, select the correct target set (in my case the Quadrant set) and ensure that clearing the selection adds all values to set - this will ensure that when a quadrant is not selected, that the relationship between primary and secondary data source contains everything possible.
What next?
You now have a dashboard on which you can filter between blended data sources at a level not contained in the secondary data source. Enjoy!
Originally posted on https://warumdatum.wordpress.com/