Forum Discussion
Best Way for work with Multiple Fact Tables
Certain, itchyeyeballs Phil_Seamark you are right, but when working with multiple fact tables that have multiple tables dimension in common, to relate, circular references are generated, resulting in inactive relationships. then I am aware that even the measures I can use the inactive relationships with USERELATIONSHIP function, but do not think that's the best way. I was considering working a technique used in QLIKVIEW called "Linktable". This basically is to create an additional table that centralize the keys of dimensions that have in common the fact tables fields. But I'm not sure if it is the best in Power BI. Perhaps someone with more experience in modeling could clarify this.
I'm not sure how you are hitting an issue with circular references, can you post some more specific detail on your model?
Ensure you have common links from the fact tables to the dimension tables you are using and then make 100% sure you are using the dimension tables to populate rows, columns and slicers etc
Using a link table in PBI is not best practice and will fall over if it results in a many to many join (all joins need to be 1 to many)
If you need to join fact and dimension tables on multiple fields (i.e. more than one link between each pair) then you will have to create a compound link field as PBI will only accept 1 active link at a time. For example you could merge "year" and "department" fields into a single field and link on that.
The current model I'm working on has 4 fact tables and 8 dimensions, I have not hit any issues so far
itchyeyeballs the problem isn't to join fact and dimension tables on multiple fields, as this is solved easily by concatenating the fields, the issue is when there are two or more fact tables that need to join to several dimension tables in common, becasue this generate some inactive relationships, causes to the circular reference. This diagram illustrates more or less what I say
I cant see anything wrong with the layout you propose in the diagram, although I have had similar issues in the past with inactive relationships due to what I assume is a bug. To resolve I had to remove all the tables and reimport them without automatic linking then recreate the links manually.
edit - one other thing to check is that your cross filter direction is set to single
Edit 2 - mine is shown below, all the dimensions are at the top
