Create Relationship Dialog Box
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
The Create Relationship dialog box enables you to create relationships between tables in your PowerPivot workbook or to edit existing relationships. To access this dialog box, on the Design tab, in the Relationships group, click Create Relationship.
For more information about what a relationship is and how creating relationships can help you create more useful data models, see Relationships Overview.
Table
Select the first table in the relationship. This should be the many side of a many-to-one relationship. For example, if the relationship is between a Customers table and an Orders table, the Orders table should be specified for Table, and the Customers table should be specified for Related Lookup Table. A customer can have many orders, but each order is for a single customer.Note
Each table can have only one relationship to another table. Therefore, if you have already used a table in a relationship with the currently selected table, the other table will not be available in the dropdown list for Related Lookup Table.
Column
Select the source column that will be used in the relationship.Related Lookup Table
Select the second table that will be in the relationship. This table should be on the one side of a many-to-one relationship.Related Lookup Column
Select the column in the second table that is related to the first column. For every row in the Related Lookup Table, this column must have a unique value.Active
The Active checkbox appears when you are editing an existing relationship. In cases where multiple relationships are possible (that is, a column in the source table matches two or more columns in the lookup table), this checkbox indicates whether the current relationship is the active relationship, used by default in DAX calculations and Pivot table navigation. For more information, see View and Edit Relationships.
Duplicate Values and Other Errors
If you choose a column that cannot be used in the relationship, a red X appears next to the column. You can pause the cursor over the error icon to view a message that provides more information about the problem. Problems that can make it impossible to create a relationship between the selected columns include the following:
Problem or message |
Resolution |
|---|---|
The relationship cannot be created because both columns selected contain duplicate values. |
To create a valid relationship, at least one column of the pair that you select must contain only unique values. You can either edit the columns to remove duplicates, or you can reverse the order of the columns so that the column that contains the unique values is used as the Related Lookup Column. |
The column contains a null or empty value. |
Data columns cannot be joined to each other on a null value. For every row, there must be a value in both of the columns that are used in a relationship. |