Relationship May be Needed message - when I've already created a relationship

Question

I have 2 tables in PowerPivot

Actuals 

EmployeeID

WeekEndingDate

Hours

EmpIDWeekEndingKey  (created as a calculated field by combining the 2 fields in PowerPivot;  Unique in this table) 

Forecast

EmployeeID

EmployeeName

Region

Project

WeekEndingDate

Hours

EmpIDWeekEndingKey (created as a calculated field by combining the 2 fields in PowerPivot;  Not Unique in this table)

I created a relationship in PowerPivot using the EmpIDWeekEndingKey.  All good.

I try creating a pivot table

• Hours from Actuals to Values
• EmployeeID from Actuals to Rows

This works just fine – actuals are shown for each employee.

If I try adding Region from Forecast to the Row Values, I get the dreaded “Relationship may be needed” message, and each region has the same summed up value.

It’s like it’s not detecting the relationship I created.  Am I doing something wrong? 

-Henry

Answer 1

Your Actuals are not Region-specific. (There is no Region field in the Actuals table. Only the Forecast table has a Region field.)

Answer 2

Henry -

Your existing relationship is from the many side (Forecast) to the 1 side (Actuals).  PowerPivot can only use the relationship in that direction to lookup a single value for a given row.  So when you try to create a pivot based on data from the Actuals table and attempt to lookup from the 1 side to the many side, the engine can't use that relationship to find a single row/value.

To solve this, you could go the old-school-Excel route and just slam all of your data into one dump table.  Another more flexible approach is to dimensionalize your data model so that the engine can lookup the common Employee and dates from both Actuals and Forecast.  Like this:

Break Employee out into it's own dimension (or lookup table) along with all of the other columns that are common across both Actuals and Forecast (EmployeeName, Region, Project).  And create a common date table (dimension).  Then you will be able to create the required many-to-one relationship from Actuals and Forecasts to lookup the Employee attributes and the common dates. 

With that done, you'll be able to slice by Region in pivots on Actuals or Forecasts or both at the same time.  Here's what you should get:

Hopefully I understood your description of your data correctly.  Post more details if you need additional guidance.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 3

Thank you, Brent.  I will try that approach.