Forum Discussion
Best Way for work with Multiple Fact Tables
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
itchyeyeballs Thanks for your replys, are very helpful, So I am going continue investigating about this, because I think there must be some modeling technique to get a star schema, even in complex models.
Try concatening them in just 1 big fact table. I used this technique in a model with 5 fact tables and it worked great.
You can add a column with an identifier for each concatened fact table so when you use a formula you can filter in DAX only the rows that met the condition for the fact table you want.
Tell me if it works for you.
Cya
