How to link 4 tables with ID number in each table

[powerpivotlegal]

Hello,

I want to be able to join 4 tables using a timekeeper ID as the bridge/commonality between all 4.

Table 1
TK ID
Multiple rows of Fees for each TK ID

Table 2
TK ID
Multiple rows of Costs for each TK ID

Table 3
TK ID
Status (Approved, Not Approved) - only 1 status for each TK ID

Table 4
TK ID
Multiple rows of Office Location for each TK ID

I have been able to join the first 3 by using Table 3 as the Lookup Table (as there are not multiple rows with the unique TK ID) and creating a relationship on TK ID. The resulting PivotTable is then able to summarize Fees, Costs, and Status by TK ID.

However, after I create a relationship between Table 3 and Table 4 on TK ID, the PivotTable will not accurately return all the Office Locations for each TK ID. It will just return all the same locations for each TK ID (e.g. return 7 locations for TK #1 even though TK #1 only worked in Bangkok and Hong Kong).

What am I missing?

Many thanks.

 

[scottsen]

Make sure that the column you "use" (put on rows/columns of a pivot table, or manipulate in a CALCULATE, etc) is from the lookup table. The other thing I would double check is the direction of the relationships -- maybe you had a 1 to 1 and it's going the wrong way?