PowerPivot doesn't recognise relationships

[StevieJD]

I'm trying to learn PowerPivot and have created two tabs In my PowerPivot window: Patient and Procedure.

The fields in the patient window are: PatientID, PatientFirstName and ProcedureID.

The fields in the Procedure table are: ProcedureID, ProcedureName and Cost.

Each patient can have many procedures and therefore there can be duplicates in the patients table (and therefore the ProcedureID can appear multiple times in the patients table), but the Procedure table has only one row for each procedure (there are four) and so the ProcedureID in the Procedure table is unique.

I have created a relationship between the two tables on ProcedureID with Patient as the Table and Procedure as the lookup table. I have also checked that the relationship exists and is active via Manage Relationships.

When I build the Pivot table, and include PatientFirstName (from the Patient table) and ProcedureName (from the Procedure table) in the Row lables, it appears as if every Patient has had all four Procedures although only one patient actually has had all four procedures.

Please can anybody tell me what I'm doing wrong?

Thank you.

 

[nekodesu]

StevieJD,

I had this same problem. I couldn't figure out why PowerPivot wasn't working even though I had a relationship defined and active. I don't mean to send you away from the Mr. Excel site, but I found a good answer here. The author provides two quick fixes. The first one is not helpful because it essentiall says "don't use data from different tables!" The second fix works however. Essentially he suggests calculating an extra colum based on your second table. So for you, you'd have PatientID, PatientFirstName, ProcedureID, ProcedureName, and Cost all in one table - you accomplish this by creating calculated fields in one of your tables. Essentially you're having PowerPivot do a big Vlookup to get all your data in one table.

Check the the link, and let me know if it helps.

T.

 

[StevieJD]

T,

Thank you very, very much for the link - I tried it and the Pivot table shows exactly what I originally expected it would

Now all I have to do, is try and understand why it didn't work

 

[RoryA]

Did you put any fields in the data area?

 

[StevieJD]

Hi Rory,

Originally I did - the cost field. However, when the results where simply the sum of the costs for all four procedures I removed it. This didn't help, so I tried deleting the pivot table, checking the relationship was active, and then rebuilding it from scratch as described above.