cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
manojsv16 Regular Visitor
Regular Visitor

Relationship between Fact to Fact tables

Hello All,

 

I am working on to develop a report which has 3 fact tables and multiple dimension tables. Please refer the below screenshot for relationship layout of tables.  I wanted to connect fact to fact tables (v_Fact1_c -> v_Fact2_c -> v_Fact 3_c). When i try to connect the fact tables, Few of the visuals are displaying errors and identified the errors are due to the relationship established between fact tables. Also, few visuals loads with inacurate data. 

 

If i established the relationship between (v_Fact1_c & v_Fact2_c) is One-One (which is the actual relationship based on the data), I am getting error in visuals. When i change the relationship between (v_Fact1_c & v_Fact2_c) to Many-One relationship (Which is not the desired one), then i am not getting any errors, but data is inacurate.  I would appreciate any suggestion on optimizing the relationship between fact to fact tables and resolving the errors.   

 

Appologize for naming convention used for the tables. Relationship Issue _ Layout.PNG

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Relationship between Fact to Fact tables

It's usually better to avoid fact to fact joins. If fact_1 and fact_2 are logically related 1 to 1 this means you could inner join them and just create one large "virutal" fact table.

 

For fact_3 if it is actually a 1 to many relationship what you could do would be to use the join column to copy all the dimension keys from the other two facts down to fact_3 then you would just join the dimensions to the facts and remove the join between fact_3 and the other fact tables. Then any time you filter on one of the dimensions both facts would get filtered.  

View solution in original post

synergised Member
Member

Re: Relationship between Fact to Fact tables

google "power bi multiple fact tables" there are several videos on how to easily do this.  We ended up creating a linktable and two fact tables which works good.

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Relationship between Fact to Fact tables

It's usually better to avoid fact to fact joins. If fact_1 and fact_2 are logically related 1 to 1 this means you could inner join them and just create one large "virutal" fact table.

 

For fact_3 if it is actually a 1 to many relationship what you could do would be to use the join column to copy all the dimension keys from the other two facts down to fact_3 then you would just join the dimensions to the facts and remove the join between fact_3 and the other fact tables. Then any time you filter on one of the dimensions both facts would get filtered.  

View solution in original post

synergised Member
Member

Re: Relationship between Fact to Fact tables

google "power bi multiple fact tables" there are several videos on how to easily do this.  We ended up creating a linktable and two fact tables which works good.

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 46 members 970 guests
Please welcome our newest community members: