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 IV
Super User IV

@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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.