View and Edit Relationships
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
The Design tab in the PowerPivot window contains tools to help you view and manage table relationships that have been defined in your workbook. For information about how relationships are used in PowerPivot for Excel, see Relationships Overview.
Note
In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.
View and Edit Existing Relationships
In the PowerPivot window, click the Design tab, and in the Relationships group, click Manage Relationships.
In the Manage Relationships dialog box, if you want to change a relationship perform these steps:
Select the relationship and click Edit.
In the Edit Relationship dialog box, change the tables or columns used in the relationship, and then click OK.
In the Manage Relationships dialog box, click Close.
Change the Active Relationship in Diagram View
If you import tables that have multiple relationships, or if you manually create multiple relationships between two tables, you must specify one relationship to be the active relationship that is used by default for navigation and calculations. The remaining relationships exist in the model as inactive relationships. You can use an inactive relationship in DAX calculations. You can also promote an inactive relationship to be the active relationship.
In Diagram View, locate the table that has active and inactive relationships. An inactive relationship appears as a dotted line. (A relationship is inactive if an indirect relationship already exists between the two columns.)
Click the active relationship line (the one that is not dotted) to select it. Right-click the line, and then right-click the selected link. Select Mark as Inactive.
Point to the dotted line between the two tables.
Right-click the dotted line, and then click Mark as Active. Notice that you can also delete and edit the relationship from its context menu.
Considerations for Editing Relationships
When editing a relationship, consider the following:
There can be only one active relationship between each pair of tables. In cases where two tables can be related in multiple ways, one relationship is the active relationship. Other relationships are preserved in the data model, but are flagged as inactive.
The active relationship is used by default in DAX calculations and Pivot report navigation. Inactive relationships can be used in DAX calculations via the USERELATIONSHIP function. For more information, see USERELATIONSHIP Function (DAX) and Working with multiple relationships between two tables.
The data values in Related Lookup Column must be unique. In other words, the column cannot contain duplicates.
Each table must have a single column that uniquely identifies each row in that table.
The data types in Column and Related Lookup Column must be compatible. For more information about data types, see Data Types Supported in PowerPivot Workbooks.
For a complete list of requirements, see "Requirements for Relationships" in Relationships Overview.