Export (0) Print
Expand All

Troubleshoot Relationships

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

This topic provides troubleshooting information for automatic relationship detection in PowerPivot for Excel. To read about the requirements for relationships that are created manually, see "Requirements for Relationships" in Relationships Overview. For information about how automatic detection works, see "Automatic Detection and Inference of Relationships" in Relationships Overview.

The following sections describe some common scenarios in which PowerPivot for Excel cannot detect relationships.

Message: No relationships were detected

The notification bar will always display a message indicating that a relationship is needed when you drag a field into the Values area of an existing PivotTable, and that field is not related to any of the fields already in the PivotTable. However, sometimes a relationship cannot be detected after this notification. There can be various reasons for this:

  • The relationship detection algorithm depends on the foreign key column having a name similar to the primary key column. If the column names are not sufficiently similar, we recommended that you open the PowerPivot window and manually create the required relationships between the tables.

  • The data types might not be supported. If any of the tables used in the PivotTable contain columns of non-supported data types only, no relationships can be detected. In this case, you should use the Create Relationships dialog box to manually create the required relationships been the active tables in the PivotTable. For more information, see Create a Relationship Between Two Tables.

Message: No Further Relationships Can Be Created

This message might appear when you try to create a relationship. In this case, it is likely that a relationship already exists between the two tables that you are trying to join.

PowerPivot for Excel generally auto-detects and creates relationships when you import tables together from a relational data source. Because no more than one relationship can be created between any two tables, you probably already have a relationship defined between the tables that you selected. You can review the list of existing relationships by using the Manage Relationships dialog box, as described in View and Edit Relationships.

You added unrelated fields to the PivotTable but no message appeared

Automatic relationship detection is triggered for measures only, not for calculated fields that you use in the row or column labels of a PivotTable. Therefore, as you begin to build your PivotTable, you might add unrelated tables but not see any relationships until you drop a field into the Values area.

No valid relationship between tables

In some scenarios, the tables that you add to the PivotTable simply cannot be joined to any other tables. For example, two tables might happen to have partially matching data, but actually have no logical relationships to the other tables already in use.

If you add a table to the PivotTable that cannot be joined to the other tables, usually automatic detection will not return any results. In other cases, you can generally tell from the results in the PivotTable that the fields do not produce meaningful calculations.

Automatic detection created the wrong relationships

When relationships are created by using automatic detection, an algorithm creates a list of all possible relationships, based on values in the tables, and ranks the possible relationships according to their probability. PowerPivot for Excel then creates only the most likely relationship. Therefore, if your tables contain multiple columns that might be used as keys, some relationships might be ranked lower and will not be automatically created even if the relationship is valid.

If the automatic detection algorithm suggests a relationship that does not solve your business problem, you should delete the relationship and manually create the relationship using the correct key column.

Community Additions

ADD
Show:
© 2014 Microsoft