Define Relationships Between Tables

March 13, 2023 - by Bill Jelen

There are three different ways to define a relationship in Power Pivot.

Say you want to link from the ProdID field in the Fact table to the ProdID field in the Products table. Follow these steps:

• 1. Go to the Power Pivot window.

• 2. Click on the sheet tab for Fact

• ---

  ---

  3. Place the cell pointer anywhere in the ProdID field.

• 4. Go to the Design tab in the Power Pivot ribbon. Select Create Relationship.

• 5. There are four fields to fill in. The first two fields area already filled in because of steps 2 & 3.

• 6. Open the Related Lookup Table dropdown and choose Products.

• 7. In most cases, Power Pivot will automatically fill in ProdID for the fourth field. If it does not, open the last dropdown and choose the ProdID field.

• Another way to build a relationship is through the Diagram view. On the Home tab in Power Pivot, click Diagram View. Drag from the Date field in the Fact table to the Date field in the Date Table to establish a relationship.
  

Gotcha: This diagram view acts differently than the one in Microsoft Access. After you have created relationships, the arrows generically point from one table to another. They do not point specifically to the linked field. To see the fields, you have to click on an arrow and the fields will be outline in blue.

Gotcha: Diagram view is slow and clunky. I feel like my computer is going to crash when I use it. I really prefer the two-click ease of building relationships discussed on the previous page.

To get back to the grid view, click the Data View icon in the Home tab.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ryunosuke Kikuno on Unsplash