Forum Discussion
Join Multiple Fact tables with common fields
Good Day,
I trust this message finds you well. I need assistance.
I currently have 3 Fact tables: Sales MTD which has 2 Months of data (Current Month and Previous Month) Sales YTD which has 12 months of data up until previous month and then I have Collections table which has 12 months of data. Each of these 3 tables have common fields which are:
1. Month End Date
2. Channel
3. Operations Manager
4. Regional Manager
5. Business Manager.
I would like to join the 3 fact tables into 1 table however I am encountering an issue. Also when I try to create a relation between the Collections and Date it says "You can't create a relationship between these two columns because one of the columns must have unique values" Which does not makes sense as the Date has unique value.
Please see screenshot below of the model. May you please assit me in joining the the fact tables. Thanks in advance
1 Reply
- amitchandak
Super User
Anonymous , if the data in fact is not overlapping, then you can append these tables
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
for rest you can have one or more common dimension like channel, operation manager etc and and join with these facts with 1-M join.
refer how to build such dimensions
Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19
else joined month end date /date in all tables with date dim
