Forum Discussion
Modelling or Merging? Link separate fact tables...
Hi all,
I have a question that either I might be over thinking or cant/shouldnt be done.
I have some data that breaks up any given day depending on what the task is e.g. below;
Table 1.
| Date | ID | Tasks | Time (hrs) |
| 12/07/20 | ID001 | Task 1 | 3.5hrs |
| 12/07/20 | ID001 | Task 2 | 7hrs |
| 12/07/20 | ID001 | Task 3 | 1.5hrs |
| 12/07/20 | ID001 | Task 4 | 2hrs |
| 12/07/20 | ID001 | Task 5 | 10hrs |
Then I have another dataset which has the same ID column 'ID001' but with a different metric at the 'single date' level (not broken down) I want to 'merge' with the above table.
Table 2.
| Date | ID | Widgets |
| 12/07/20 | ID001 | 5,300 |
| 13/07/20 | ID001 | 2,600 |
| 14/07/20 | ID001 | 4,600 |
| 15/07/20 | ID001 | 4,400 |
Because these 2 separate 'fact tables' break down the metric by date and slice of a date I'm at a loss in how to merge these 2 datasets together (by ID & Date) as I'm trying to determine how 'Widgets' are affected by 'Tasks' (as an example). Merging Table 1 with Table 2 in Power Query just duplicates the 'Widgets' values and I am unable to use the 'Key Influencers' visual because there is no relationship between the 2 fact tables.
Any ideas, or have I completly confused everyone?
Thanks! 😀
Hi shane7mcdonald ,
You can try to create a bridge table and create relationships between two fact tables like this:
Refer: Many-to-many relationship guidance
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi shane7mcdonald ,
There is one thing that I got lost in your explanation but for sure you can explain, you have a date table and do you have an ID table, does any of the table that you have links the ID between both tables?
If you create two dimension tables Date (that you already have) and ID (not sure if is any of the ones you have) then you can just create measure to make the calculations.
I don't know how you are trying to check both values but something similar to:
Difference widget - Hours = SUM(Table[Widgets]) - SUM(Table[Hours])The formula above will make the subtration betweed both tables.
Hi shane7mcdonald ,
You can try to create a bridge table and create relationships between two fact tables like this:
Refer: Many-to-many relationship guidance
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
