Relationship between Fact to Fact tables

[manojsv16]

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.

[d_gosbell · Accepted solution]

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.  

[synergised · Accepted solution]

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.

[d_gosbell · Accepted solution]

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.