Forum Discussion
Best Way for work with Multiple Fact Tables
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.
Sorry, I didn't mean that inactive relationships were a bug, what was happening in my case was there seemed to be ghost relationships that I couldn't see or get rid of, PBI kept making my chosen relationships inactive even though there wew no other relationships between the tables.
I conciously avoid having more complex relationships when working with multiple fact tables, even the experts have similar concerns (note about two way relationships half way down the article) http://www.sqlbi.com/articles/the-space-report-under-the-hood/?utm_source=wysija&utm_medium=email&utm_campaign=4-2016