PowerPivot Data Model Causing Cartesian Product

Summary

Using a two table PowerPivot data model, I'm not able to create a pivot table that has multiple rows filtered in the same way a single table does using the same columns.

Details

I have a simple two table data model:

• Table 1 - Employees
• Table 2 - Office

When I create a pivot table with this model I can list all of the Offices and display the correct value for the number of employees in each office. This works as expected.

PROBLEM

When I put a column from each table into the rows field, it does not display the expected result. Instead of listing all of the offices and related employees for each office, it displays all of the employees each time.

When I create a table with the Office and Employee data in the same table, I get the expected results.

Question

Is it possible to get the same results in a pivot table with two tables in powerpivot that I get with a single table as demonstrated above? Right now I'm assuming this is either a limitation or I'm missing a setting somewhere.

Create a measure and drop that in a field. The default behaviour is a Cartesian product until you drop a measure in.

With no measure:

After adding a measure: