Filter Data Across Multiple Data Sources
While working with multiple data sources in a workbook, you might want to compare the data between them using a field they have in common. To do so, you can apply a filter across multiple primary data sources.
For example, the following dashboard shows the order quantity, average sales, and average profit for customers. It has three views. Each of the views uses a different data source as its primary data source, and those data sources all have a field in common: Customer Name. There is also a filter card in the view for Customer Name.
This is an interesting dashboard with a lot of great information, but you might want to update all of the views in the dashboard at the same time by the customer you’re analyzing. For example, maybe you want to see the average sales, profit, and number of orders you’ve received from one of your customers, Aaron Riggs.
To do so, you can filter all three data sources on the Customer Name field.
Follow the steps below to learn how to filter data across multiple data sources.
Before you start, you might want to know the following:
Starting with version 10.0, you can filter data across multiple primary data sources. You cannot filter data across secondary data sources. Filtering data across a worksheet's secondary data source is not currently supported in Tableau Desktop.
If you want to filter data across secondary data sources, consider the following alternatives:
Join tables that are in the same data source, instead of blending them. For more information, see Join Your Data.
Create a cross-database join if your tables are in different data sources. For more information, see Join Your Data.
Make the primary data source the secondary data source, and the secondary data source the primary. Note: This might not always be feasible given the level of detail you want in the final view.
Note: To ensure the data strings with mixed capitalization are treated as case-insensitive in the filter, create a calculated field using the UPPER() string function, and then create the filter relationship using that calculated field.
For more FAQs about cross data-source filters, see the Cross data-source filtering FAQs forum post in the Tableau Community.
: Define relationships between your data sources
Before you can create relationships between data sources, you must ensure that there is a common field between the data sources you're mapping. The fields do not need to be named the same in each data source, but they should have some data in common.
After you've identified the common fields, you must create relationships between them, or map the fields to one another.
If the fields happen to have the same name, Tableau might recognize them as related automatically. You can modify an existing relationship that was created automatically by Tableau, or create a new relationship between two fields in different data sources, by following the procedure below.
To define a relationship between your two data sources:
Select Data > Edit Relationships.
In the Edit Relationships dialog box, do the following:
Select a data source for Primary data source, and select a data source for Secondary data source.
To define a relationship between fields that have different names, click Add.
To edit an existing relationship, select the fields on the right, and then click Edit.
In the Add/Edit Field Mapping dialog box, do the following, and then click OK:
Under Primary data source field, select a field.
Under Secondary data source field, select a field with similar data to the field you selected for the primary data source.
For more information about editing relationships, see Blend Your Data.
Note: After you define your relationships, you do not need to enable blends (that is, you do not have to click the link icon in the Data pane) to filter across your data sources.
For more information about the difference between primary and secondary data sources, see Blend Your Data.
: Add a filter to the view
After you have defined relationships between your data sources, go to one of your worksheets and drag a dimension to the Filters shelf. Then select to include or exclude data from the view. For more information about filtering your data, see Filter Data from Your Views.
Optional Step: Display a filter card in the view. Right-click the field on the Filters shelf and select Show filter. A filter card for that field appears in the view.
For more information about filter cards (previously known as quick filters), see Display interactive filters in the view.
: Apply the filter to your worksheets
After you set up your filter, you can apply that filter to all the worksheets in your workbook that use related data sources, or you can apply it to selected worksheets.
To apply the filter to all worksheets that use related data sources, right-click the field on the Filters shelf and select Apply to worksheets > All using related data sources.
To apply the filter to selected worksheets, right-click the field on the Filters shelf and select Apply to worksheets > Selected worksheets.
The Customer Name field is added to the Filters shelf on every worksheet that uses a related data source. An icon is added next to the field on the Filters shelf, indicating that the filter is being applied to multiple data sources.
If you hover over the field on the Filters shelf in any worksheet, you can find details about the type of filter, the source field for the filter, and the other sheets the filter applies to.
The Customer Name field is added to the Filters shelf on the worksheets you selected. An icon is added next to the field on the Filters shelf to indicate that the filter is being applied to select worksheets.
Now, on the dashboard, when you filter the view down to Aaron Riggs, all three views update and you can see that Aaron made orders in 2010, 2011, and 2013, and spent an average of 3,700 USD. The average profit for these orders was approximately 1,000 USD.
When you apply a filter to multiple data sources, you create a source field and one or more target fields. Both source fields and target fields appear on the Filter shelf in their respective worksheets.
The source field is the field you're filtering with.
The target field on any given worksheet is a field from another data source that is related to the source field. This is the field that is actually being filtered on that worksheet.
The source field determines the data that is included or excluded from the target fields. For example, let's say you have three worksheets that use three separate data sources (A, B, and C) as their primary data source. Each of the data sources has a field in common (Fruit), and the data is as follows:
|Fruit from data source A||Fruit from data source B||Fruit from data source C|
If the Fruit field from data source A is the source field for the cross data source filter, then the data that appears for the target fields is as follows:
|Fruit from data source A - Source field||Fruit from data source B - Target field||Fruit from data source C - Target field|
Any data that does not match the data in the source field is excluded from the target fields, and will not appear in your worksheets or in your filter cards.
On the Filters shelf, source fields are indicated with either a icon when the source field applies to all worksheets with a related data source, or a icon when it applies to select worksheets. Target fields are indicated with a icon on the field on the Filters shelf. They are also indicated with a or icon next to the field on the Filters shelf.