Skip to Content

How to Filter Across Multiple Data Sources in Tableau – 3 Simple Methods

Blog | April 8, 2018 | By Vijai Narasimha

Filter Tableau Data Source, Filter Option, Filter Action, Global Filter, Tableau Filter Pane, Action Filter

Using multiple data sets / multiple data sources has been one of the most important concepts in Data Analysis/Analytics. It is right there in the name: Analysis of Data with more than one Data Source. Data sources can Join, where they are combined, based on a unique identifier, or a Union to append multiple tables (to obtain a long columnar data)- or just build different graphs from the different data source (to evaluate data consistency). The Data Source can further be a Blend to show values based on any matching field when Joins are failing. 

With Tableau, Blends have been one of the most important and difficult concepts to understand and execute. Tableau does a great job in providing color and visual cues to easily identify the Main Source (Primary) and Dependent Sources (Secondary) after fields from both data sources come into the view. 

Many of us, after using blends in a variety of cases still have issues with them and are constantly trying to resolve them with hacks and tricks. One such case is or was ‘Filtering across Data Sources’ and it is a huge necessity for most organizations. With Tableau Dashboard as a tool constantly evolving and adding more features for every new version and release, Blends and Cross Data Filters have found significant improvements.

Before we jump into everything, a quick review of Blends: 

  1. Blends occur with 2 or more Data Source(s) in the view (Not in the data connection window). 
  2. There can be one Primary Source that has a Blue checkmark identifier or cue. 
  3. There can be multiple Secondary Sources that have an Orange Checkmark cue. 
  4. Between the sources, there has to be at least one common field with an active relationship. 
  5. Usually, the Secondary Sources depend on the Primary Source and mimic a Left Join. 
  6. Usually, the Primary Source controls the view, so the auto-generated fields: Measure Names, Measure Values, Latitude (generated), and Longitude (generated) are used only from the Primary Source. 

In this example, we will be demonstrating a Cross Data Source Filter which can be achieved in 3 different ways. All these methods have their pros and cons. But we have something that can be appreciated. 

For this purpose, we are taking two sample files. One is an Excel File with Actual Sales for States in the US divided into Regions. Another is a CSV file with Forecast Sales for States in the US, divided into Divisions. 

This is important because as mentioned before, there has been an active relationship between common fields. If the fields are named the same, Tableau will automatically create a relationship, else we as developers have to establish the relationship. The easiest way would be to name the required fields the same. 

The concept is very simple and basic. 

  1. We shall create two visualizations 
  2. One viz with Actual Values for all States 
  3. Another viz with Forecast Values for all States 
  4. Finally, when assembled on the Dashboard, both views must be controlled by the same filter – either Region or Division making the filter work as Global Cross Data Source Filter 
Visualizations with Actual & Forecast Values
Figure 1:Excel of Actual Values for all States and Forecast Values for all States. 
Global Cross Data Source Filter - Actual Values
Figure 2: Tableau Dashboard – Actual and Forcast values
Global Cross Data Source Filter - Forecast Values

Method 1 – Filter Across Multiple Data Sources in Tableau

This is one of the easiest and the most widely used methods to achieve a Cross-Data Filter. Note: This is a new feature in Tableau 10.X – for earlier versions of Tableau, we had to use different methods to achieve this, which we will talk about below. 

1. Starting a view of Actual Sales for each State and Region filter. 

Data Source Filters Actual & Forecast Values with Dimensions and Measures
Figure 3:Actual Sales for each State and Region 

2. Another view of Forecast Sales for each State without a filter. 
(We should also note that States are not the same) 

Arranging Actual Data on a Dashboard with State and Region Filter
 
Figure 4: Forecase Sales for each State and Region 

3. Arrange them on Tableau Dashboard 
At this point, the Region Filter Condition will not apply to the second view (Forecast sheet – all values are displayed)- we will need to activate it. 

Arranging Forecast Data on a Dashboard with State Filter
Figure 5: Method 1 

4. Click on the drop-down column of the filter. We now need to apply the required worksheets or apply to all related sources. 

Activate Filter Forecast Sheet
 
Figure 6: Apply to Related Data Source 

However, the filter data condition will still not work. The reason for this is because Tableau does not realize that the Region is related to the Forecast Data set. But since we have studied both files, we know that the Region field is the same as the Division field. 

On any sheet, using the Data menu, we need to create (customize) a new relationship. We will notice that the State is in a ready relationship because of the name. 

Activate to Worksheet or Related Sources
 
Figure 7: Data Menu 
Data menu - customize a new relationship
 
Figure 8: Customize New Relationship 
 Customize relationship with Primary & Secondary Data Source
Figure 9: Add / Edit Field Mapping 

Immediately, the Region Filter Option on the Tableau Dashboard will be active. Only one member is selected to display.  In the previous step, it was applied to all related data sources i.e. all sheets from this workbook will be activated for the Region Filter. If we need to restrict this activity, we can apply it to the required sheets.  

Add/Edit Field Mapping
Figure 10: Apply Filter to Worksheets (Region) 

Also, at this point, the second view which did not have a filter will show a Secondary Source filter (orange checkmark) 

Dimensions Create Parameter
Figure 11: Secondary Source  
A Parameter behaving as a filter for data sources
Figure 12: Method 1 

Method 2 – Filter Across Multiple Data Sources in Tableau

For this method, a Parameter will be used to filter across sources. This was the method used before Version 10.X. But a drawback is that it will only be a single value selection. But some developers prefer this approach as it performs faster than a filter because of the better cache rate hit ratio.  

The concept is that there will be a single Parameter, but each source will have its Calculated logic which activates the Parameter. In this method, the relationship need not be established as Blend features are not used. 

  1. Starting a view of Actual Sales for each State. 
  2. Another view of Forecast Sales for each State.
    (Both views will not have any filter at this time) 
  3. Arrange them on a Dashboard.  
Calculated field for the Data with Actual Values
Figure 13: Method 2 

4. Create a Parameter with the following features: A String Parameter as a list with the members of Region or Division. The “All” option can be included if needed but needs a small extra line in the Calculation that is written later.

Region Filter – Select from the list
Figure 14: Create a Parameter 
Figure 15: Create a Parameter 1.2 

5. Create a Calculated Field for the Data Source containing Actual Value. This Calculation will match the Parameter members with the Region field members. If it is an exact match, then they will be displayed. 

Figure 16: Create a Calculated Data Field 

6. Use it as a True filter 

Figure 17: Use it as a True Filter 

7. We need to repeat the process for the Data Source containing Forecast Values. First, a Calculated Field and then using the True Filter. 

Figure 18: Repeat the Process 

8. The Last Step would be to show the Parameter control for the End User on the Dashboard. 

Figure 19: Method 2 

Method 3 – Filter Across Multiple Data Sources in Tableau

For this method, a Dashboard Action will be used to Filter across sources. This was the method used before Version 10.X. This can be a single value selection or multiple value selection by using the CTRL-key.  Some developers use this method as it looks more dynamic and immersive for users on the Dashboard since they have the freedom to customize the shape or the chart etc.  

The concept is that the Field to be used as a filter will be used as a sheet on the Dashboard. In this method too, the relationship need not be established as Blend features are not used. 

  1. Starting a view of Actual Sales for each State. 
  2. Another view of Forecast Sales for each State. 
    (Both views will not have any filter at this time) 
  3. Arrange them on a Dashboard. 
Figure 20: Method 3 
Figure 21: Region / Division Filter 

4.  Create a new sheet. Either use the Region field or the Division field. Not both. We can then customize them however required. For this example, to keep it simple, we are using a circle shape.

5.  Use the sheet on the Dashboard, however the filter is not activated yet. 

Figure 22: Sheet on Dashboard without Filter Active 
Figure 23: Dashboard dropdown for actions 

6.  Add Dashboard actions.  
This is the most important step. However, we need to be careful about target fields. We proceed to apply two Dashboard actions; the first for Actual and the other for Forecast. The Dashboard action for Actual is straight forward as in this case, the source and target are created from the Actual Data Source. The Dashboard Action for Forecast needs a field mapping. 
 

If you have any query regarding this Blog, please feel free to write to us at, resources@useready.com– one of our data scientists will reach out to your shortly.  

author image
Author
Vijai Narasimha
Back to top