Working with multiple tables makes data more interesting and relevant to the PivotTables and reports that use that data. When you work with your data using the Power Pivot add-in, you can use Diagram View to create and manage the connections among the tables you imported.
Creating table relationships requires that each table has a column that contains matching values. For example, if you are relating Customers and Orders, each Order record would need to have a Customer Code or ID that resolves to a single customer.
-
In the Power Pivot window, click Diagram View. The Data View spreadsheet layout changes to a visual diagram layout, and the tables are automatically organized, based on their relationships.
-
Right-click a table diagram, and then click Create Relationship. The Create Relationship dialog box opens.
-
If the table is from a relational database, a column is preselected. If no column is preselected, choose one from the table that contains the data that will be used to correlate the rows in each table.
-
For Related Lookup Table, select a table that has at least one column of data that is related to the table you just selected for Table.
-
For Column, select the column that contains the data that is related to Related Lookup Column.
-
Click Create.
Note: Although Excel checks whether the data types match between each column, it doesn’t verify that the columns actually contain matching data and will create the relationship even if the values do not correspond. To check whether the relationship is valid, create a PivotTable that contains fields from both tables. If the data looks wrong (for example, the cells are empty or the same value repeats down each row), you’ll need to choose different fields, and possibly different tables.
Find a related column
When data models contain a lot of tables, or tables include a large number of fields, it can be hard to determine which columns to use in a table relationship. One way to find a related column is to search for it in the model. This technique is useful if you already know which column (or key) you want to use, but aren’t sure whether other tables include the column. For example, fact tables in a data warehouse typically include many keys. You might start with a key in that table, and then search the model for other tables that contain the same key. Any table that contains a corresponding key can be used in a table relationship.
-
In the Power Pivot window, click Find.
-
In Find What, enter the key or column as a search term. Search terms must consist of field name. You cannot search on characteristics of a column or the type of data it contains.
-
Click the Show hidden fields while finding metadata box. If a key was hidden to reduce clutter in the model, you might not see it in Diagram view.
-
Click Find Next. If a match is found, the column is highlighted in the table diagram. You now know which table contains a matching column that can be used in a table relationship.
Change the active relationship
Tables can have multiple relationships, but only one can be active. 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).
Multiple relationships exist if the tables were imported that way, where multiple relationships were defined for that table in the original data source, or if you create additional relationships manually to support DAX calculations.
To change the active relationship, promote an inactive relationship. The current active relationship becomes inactive automatically.
-
Point to the relationship line between the tables. An inactive relationship appears as a dotted line. (The relationship is inactive because an indirect relationship already exists between the two columns.)
-
Right-click the line, and then click Mark as Active.
Note: You can only activate the relationship if no other relationship relates the two tables. If tables are already related, but you want to change how they are related, you must mark the current relationship as inactive, and then activate the new one.
Arrange tables in the Diagram view
To see all the tables on the screen, click the Fit to Screen icon in the top-right corner of Diagram View.
To organize a comfortable view, use the Drag to Zoom control, the Minimap, and drag the tables into the layout that you prefer. You can also use the scroll bars and your mouse wheel to scroll the screen.
