cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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

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

Resolver II
Resolver II

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

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

Resolver II
Resolver II

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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors