cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SaneleZwane
Frequent Visitor

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

SaneleZwane_0-1622621158858.png

 

1 REPLY 1
amitchandak
Super User
Super User

@SaneleZwane , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!