Relationships: three tables basics

[Anonymous]

Hi,

This might be basic question, but everytime I keep making the same misstake because i don't know the reason why this doesn't work. Everytime i try to built a data set, i keep running to the same issue and that is linking multiple data sets to eachother with power Pivot. I don't understand why I can't get the required information from these datasets and relationships between them.

Am I fundementally not understaning how data relationships work?

Example: 3 Tables

Example: The relationships

The Pivot tables that work

But this doesn't work?

Or even this, adding the names:

Can you please enlighten me as to what i'm doing incorrectly or how i should approach this?

With kind regards,

Ryan

[Anonymous]

Thanks Syk, it's more clear to me now! I will just join the Employee rates with Mapping IDs and then have a 1 to many connection with timesheets.

[Syk · Accepted solution]

From your data, it looks like the 'Timewrite ID' is specific to a user. If you just add that field to the employee rates table, you can remove the 'Mapping IDs' table and would likely solve your issue. Just relate the employee rates to timestamp with the timewrite ID and it should be a 1 to many.

[Anonymous]

Hi, Thanks for your answer. I know that it can be fixed that way, but why would that be a problem in the first place? Does it not work that way? I could just join the first two together, but ideally I would just use the raw data set from 2 different systems if possible.

[Syk]

If you notice the direction of the arrows on your current relationships the mapping IDs table can filter both tables. However, this means that your user table can't interact with your timesheet table WITHOUT using the mapping IDs table.

Your user table is essentially a dimension table meaning it stores the attributes (or dimensions) of the facts. The timesheet table would be your fact table that contains your actual records. You want a 1 to many relationship between your dimension and fact table if possible. Your current setup doesn't allow your best dimension table to fitler your fact table and the mapping IDs is just clutter.