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
itchyeyeballs well.. inactive relationships are not a bug, this occurs when there is ambiguity in the direction as must propagate the filter one table to another. In your case you do not have that issue because you use the filter in single direction, but this makes the filter can only go in one direction and there are certain scenarios in which it is necessary that the direction of the filter is set in both directions. At the beginning of a project it may not be necessary to use both direction, but applications and customer requirements can change over time. For this reason for a more scalable model, I prefer to use the filter settings in both directions.