This normally happens when the relationship defined between the two tables is in the wrong direction or if you try to use a column/attribute from the 'many' side of the relationship to slice a measure from a table on the 'one' side of the relationship. If you're able to provide more details on this scenario so that we can re-produce.
There are three different ways to define a relationship in Power Pivot.
Create Relationship Between Tables In Excel 2010
- But if you create a relation between 2 tables then it would be very easier to get the target data within just few seconds. And you can create your desire reports very easily. In others database program like, SQL, Access, Oracle you can create table relationship among few tables with a Primary Key and Foreign Key.
- Jan 07, 2010 Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Add more power to your data analysis by creating relationships amogn different tables. A relationship is a connection between two tables that contain data: one column in each table is the basis for the relationship.
Say you want to link from the ProdID field in the Fact table to the ProdID field in the Products table. Follow these steps:
Excel Create Relationship Between Tables Chart
- Go to the Power Pivot window.
- Click on the sheet tab for Fact
- Place the cell pointer anywhere in the ProdID field.
- Go to the Design tab in the Power Pivot ribbon. Select Create Relationship.
- There are four fields to fill in. The first two fields area already filled in because of steps 2 & 3.
- Open the Related Lookup Table dropdown and choose Products.
- 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.
Excel Create Relationship Between Two Tables
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.
Excel Create Relationship Between Tables
- Create relationships in Diagram View.
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.