Export (0) Print
Expand All

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 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.

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft