Troubleshoot Data Blending
When you use data blending to combine your data from different data sources, there are a few common issues that might arise. This topic lists them and describes how you can respond to each situation.
Common warnings and errors when blending data sources
No relationship to the primary data source
When you drag a field from a secondary data source to the view, you might see a warning that says:
Fields cannot be used from the [name of secondary data source] data source, because there is no blend relationship to the primary data source. In the Data pane, switch to the [name of secondary data source] data source, and click at least one link icon to blend these data sources.
This warning occurs when you have no active links in the secondary data source
For example, suppose you have two data sources that are related by the State and Date fields. At least one of these fields must have the active link icon (
) next to it in the secondary data source. You can make the link active by clicking a broken link icon (
) in the Data pane or by using the linking field from the primary data source in the view.
The secondary data source may not have any links to the primary data source. Look in the Data pane for the link icon. Tableau automatically links fields that have the same name. If your fields do not have the same name, you must define a link between them. For more information, see Blend Your Data.
Primary and secondary connections are from tables in the same data source
When you drag a field from a secondary data source to the view, you might see a warning that says:
The primary and secondary connections are from tables in the same data source. Instead of linking the connections, use the Data menu to join the data. Joins can integrate data from many tables and may improve performance and filtering.
This warning occurs when the workbook contains separate data sources that connect to the same database. Though you can combine data in this way, Tableau recommends that you use a join or relationship to combine data from the same database instead, as they can leverage some of the database’s native optimisations in ways that blending can’t.
Cannot blend the secondary data source because one or more fields use an unsupported aggregation
Data blending has some limitations regarding non-additive aggregates such as COUNTD, MEDIAN and RAWSQLAGG. Non-additive aggregates are aggregate functions that produce results that cannot be aggregated along a dimension. Instead, the values have to be calculated individually. All Number functions, except for MAX and MIN, are non-additive aggregates. For more information, see Tableau Functions (by Category).
These limitations cause certain fields in the view to become invalid under certain circumstances. If you hover your mouse cursor over one of these invalid fields, you see the following error:
Cannot blend the secondary data source because one or more fields use an unsupported aggregation.
This error can occur for one of the following reasons:
Non-additive aggregates from the primary data source: Non-additive aggregates are only supported in the primary data source if the data in the data source comes from a relational database that allows the use of temporary tables. To work around this issue, consider creating an extract of your data source. Extracts support temporary tables.
Non-additive aggregates from the secondary data source: Non-additive aggregates are only supported in the secondary data source if the linking field from the primary data source is included in the view and no non-aggregated dimensions from the secondary data source is included in the view. Some number functions can still be used if they include an additive aggregation. For example, use ROUND(SUM([Sales]),1) instead of ROUND([Sales],1).
Non-additive aggregates from a multi-connection data source that uses a live connection: Multi-connection data sources that connect to data using a live connection do not support temporary tables. Therefore, using a multi-connection data source that connects to data using a live connection prohibits the use of blending functionality with non-additive aggregates. To work around this issue, consider creating an extract of your multi-connection data source. Extracts support temporary tables.
LOD expressions from the secondary data source: This error can also appear when you use a level of detail expression in a view that uses data blending. To resolve the error, make sure the linking field in the primary data source is in the view before you use an LOD expression from the secondary data source and remove any dimensions, including dimension filters, from the secondary data source.
Data sources that contain logical tables cannot be used as secondary data sources for data blending
The secondary data source can use logical joins as long as the following conditions are satisfied:
- All active linking fields from the primary data source must be included in the view on a shelf other than the Filters shelf
- All fields from the secondary data source are aggregated, including dimensions
Asterisks show in the sheet
When you blend data, make sure that there is only one matching value in the secondary data source for each mark in the primary data source. If there are multiple matching values, you see an asterisk in the view that results after you blend data.
For example, suppose you have two data sources: Population and Superstore. The primary data source, Population, has a field called State. The secondary data source, Superstore, contains fields called State and Segment. In the secondary data source, each state has multiple segments. Alabama, for example, has three segments: Consumer, Corporate and Home Office.
Primary data source  | Secondary data source  |
When you blend the two data sources on the State field, you create a link where individual state values (in the primary data source) can have multiple segment values (in the secondary data source). In this case, multiple values for segments in the secondary data source for each corresponding state value in the primary data source cause asterisks to appear in the view, as demonstrated by the images below.
Blended data
Resolve asterisks in the sheet
Avoid asterisks in the sheet by making sure that there is only one matching value in the secondary data source for each mark in the primary data source. Though the way you ensure this depends on the data and the view you're trying to create, consider one of the following suggestions to resolve asterisks in the sheet.
Add field with a higher level of granularity from the primary data source to the sheet.
For example, suppose you have a view similar to the example used above. Consider adding a field such as City into the sheet because its values are at a higher level of granularity than a field like State.
Rebuild the view to switch the primary and secondary data sources with each other. In general, you should make the data source whose values have a higher level of granularity the primary data source.
For example, suppose you have a view similar to the example used above. Consider making Superstore the primary data source and Population the secondary data source.
Null values appear after blending data sources
Null values can sometimes appear in place of the data you want in the view when you use data blending. Null values can appears for a few reasons:
The secondary data source does not contain values for the corresponding values in the primary data source.
The data types of the fields you are blending on are different.
The values in the primary and secondary data sources use different casing.
Data blending works by supplementing the data in the primary data source with data from the secondary data source based on the linking field. This means Tableau takes all the data in the primary data source, and only the corresponding matches in the secondary data source. If Tableau cannot identify at least one matching value in the secondary data source for the corresponding value in the primary data source, null values appear.
You can resolve this issue by doing the following:
Insert data in the secondary data source: Insert missing data in the secondary data source so that all records in the primary data source have a match. For example, suppose your primary data source contains values for all 50 US states, but your secondary data source only has values for 30 US states. To resolve this issue, you can add values for the remaining 20 US states to the underlying data set of the secondary data source. Alternatively, you might be able to Bring a Field into the Primary Data Source or Alias Field Values Using Data Blending to work around null values if you cannot modify the underlying data.
Verify data types in the primary and secondary data sources match: For example, both data sources should use string data for the fields you are blending on, or both fields should contain numbers. You can't blend fields where one field contains numbers and the other contains strings.
Verify the casing of the values in the primary and secondary data sources match: If one data source uses a mix of upper- and lower-case names, and the other uses only upper-case, you'll see null values. For more information, see Nulls Show When Data Blending.
Blending issues after publishing data sources
When you make a field-level customisation that blends two data sources, the customisation is based on one of the data sources, the primary data source. Then, when the primary and secondary data sources are published to Tableau Server or Tableau Cloud, the primary data source, which contains the customisation (i.e. the reference to the secondary data source) no longer works. The customisation no longer works because the blend relationship between the primary and secondary data sources is lost. This is because the blend relationship between the primary and secondary data sources is defined at the workbook level.
For example, suppose you have two data sources: Store - Main and Store - West. You create a field-level customisation, like a calculation, that combines the sales totals from the primary data source, Store - Main, with the sales totals from the secondary data source, Store - West. As soon as you publish the data sources to Tableau Server or Tableau Cloud, the calculation in Store - Main no longer works. This is because the calculation refers to the secondary data source, Store - West, whose location is now undetermined.
To work around this scenario, do the following:
Before creating any field-level customisations, publish each data source first.
Connect to each data source.
Create a workbook that contains the field-level customisations that you need.
Publish the workbook to Tableau Server.
You can use this workbook as a ‘template’ that can be shared and downloaded by you and others.
Blending with a cube (multidimensional) data source
Cube data sources can only be used as the primary data source for blending data in Tableau. They cannot be used as secondary data sources.
Other data blending issues
For other issues that emerge while blending data, see the following links.
For issues sorting on a calculated field, see Sorting by Fields is Unavailable for Data Blended Measures.
For issues with a computed sort, see Sort Options Not Available from Toolbar When Data Blending.
Fields from the secondary data source cannot be added to a URL action. See Fields from Blended Data Source Unavailable for URL Actions.
Action filters are not behaving as expected. See Action Filters with Blended Data Not Working as Expected.
Duplicate totals after every date value in the view. See Issues with Blending on Date Fields.
Underlying data shows different values than blended data. See Underlying Data from Secondary Data Source Not Displayed or Consistent with Blended Data.