The Excel Data Model Relationships are Not Working

Overview

Data model relationships can be defined as a relationship that connects two different data tables.

In a data model, there are two types of relationships: one-to-one and one-to-many.

A Many-to-many relationship can’t be supported in the data model.

Solution 1 – Create Relationships Between Tables Manually

When you drag fields into the Values area in an existing Pivot Table, a notification indicates the relationship is needed. The relationship depends on the foreign key with a similar name to the primary key column. If the name is not enough, you need to create a manual relationship between tables.

The primary key is known as a unique key that identifies a row in a given table whereas the foreign key is known as a key that indicates a column that refers to a unique column in another table. You need to check if there is a foreign key with a similar name to the primary key column. Otherwise, you will get a message of no relationship detected and will have to create relationships between tables manually.

Solution 2 – Modify Unsupported Data Types

If the data type is not applicable, a message of no relationship detected or a data model relationship not working will be displayed. If any of the tables used in the Pivot Table have different data types, no relationship will be created. You’ll have to create the manual relationship dialog box or to change the data type.

Solution 3 – Make Sure There Is a Valid Relationship Between Tables

There must be a logical relationships between tables.

Solution 4 – Create a Bridge Table

The bridge table is used to create a bridge between the ‘not working’ table and the table you want to use. It can be defined as the table that reduces the gap between the fact table and the not working table at a lower grain.

The bridge table has its own primary key which represents the combination of the primary key of both tables.

Solution 5 – Add Fields into the Value Area in the Pivot Table

Automatic relationship detection can be applicable for measures only. It is not applicable for calculated fields that you use in the row and column labels in the Pivot Table. To get the relationship using the automatic relationships detector, you have to enter a field into the Value area.

Excel Data Model Relationships Not Working

Solution 6 – Detect Wrong Relationships Through an Automatic Detection

When the relationships are created by automatic detection, the detection algorithm will create all the possible relationships based on the values of the tables and rank the relationships based on their probabilities. Excel creates the most likely relationship with the help of the detection algorithm. If the tables contain several columns that can be used as keys, some of the relationships rank lower compared to others, and a wrong relationship may be created. You have to create those relationships manually.

<< Go Back to Create Relationships in Excel | Data Model in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!