Forum Discussion
Creating appropriate relationships for data model with multiple Fact tables
Hi Mattyt510 ,
The question is the fact that you have both tables making the filter for "different sides" so when you add the names and the functions to your table it does not get the correct value.
Once again I believe you need to revise the way you have your model setup but with the current setup you can force the filters for function to be overwritten by the relationships using a dax measure:
Roles / function count = COUNTROWS( CROSSJOIN('RolesFunctions (2)', 'UserRole (2)'))If you add this in you visualization you get the following result:
Hi Mattyt510 ,
Power BI is optimized to use a star schema, with one to many relationships with single filter, in you case you have several bi directional that you can see in the image below:
Some of the best practices is to in some cases join some of the dimension tables into one this depends on the information and level of granularity.
In your case I believe that the fact that you have all of these bi directional will lead to a circular relationship that breaks the model.
Hi MFelix, thank you for your advice.
Unfortunately there are many bi-directional relationships as a significant function of my report I'm hoping to have is identifying Functions associated with a User. Which in this case involves filtering across multiple Dimension and Fact tables (with how I have the model built currently.)
The bi-directional relationship between Profiles and ProfileRoles is also required to associate roles to users, where the rolea are inherited from a profile.
I have tried leaving the assoiation Fact table in its original form, however, I'm unsure how to navigate around Profiles being in two columns, as I will need to self reference the table to determine the User -> Profile -> Role relationship.
Thanks for your help.
