Excel PowerPivot Relationships

Question

I have an issue when using PowerPivot in Excel 2016.

After creating a relationship between two tables, and afterwards create a pivot table from the data model, the resulting pivot ignores the relationship.

Below you can see my two tables - Table 2 with company code and name, Table 3 with Location, Location name and related company code.

---

As you can see in the resulting pivot it lists all locations for all companies instead of only the location that actually relates to the company.

---

Here is the link created in the data model, between the Company code in table 1 and company code in table 2.

---

For completeness sake, here is the Pivot Field List used in the first picture.

---

How do i make the pivot table respect the relation that i have created between the two tables?

Here is the example file: example.xmlx

Answer 1

Put any field from Table3 into the Values area. This will give you a count of the field, but more importantly it will force the relationships into effect.

Here's an example.

Answer 2

Because the second table contain duplicated values.