Creating appropriate relationships for data model with multiple Fact tables

[Mattyt510]

Hi all,

I'm quite new to building models and reports in Power BI and would greatly appreciate some advice on modelling.
I'm having issues configuring my model to be able to determine what Functions different Users have access to in a system, without having ambiguity in the model.

This is how my model looks currently:

In this system users can be assoicated to Functions in two different ways. Either User -> Profile -> Role -> Function or User -> Role -> Function, hence the circular model.

The UserRoles, UserProfiles and ProfileRoles Fact tables have been derived from a single relationship table, which was formatted like this like below, and I split out in Power Query.

Name	NameType	AssocName	AssocType	RelationshipType
User123	U	R	Role123	User to Role
User246	U	P	Prof678	User to Profile
Prof678	P	R	Role123	Profile to Role

I've tested using the relationship as a singular relationship table, but had issues with visualising the Users -> Roles relationship when the role is associated through a profile (Such as User246 being related to Role123 in the above table), and splitting the Fact table resolved this issue.

I've also tested using two User dimension tables to split the two pathways, however, my models relationships break at the ProfileRoles table.

I can build visuals for Users to Profiles, Users to Roles, Roles to users via profiles, and Roles to Functions, however, the report breaks down when trying to go from Users or Profiles to Functions, through roles. I've played with different cross filtering but haven't had any luck.

Any help/advice is appreciated.

Regards,

Matty

[v-jialongy-msft]

Hi @Mattyt510 

Can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

[Mattyt510]

Hi @v-jialongy-msft,

Here is a sample of my data set, unfortunately I've had to create mock data in case of any sensitive content, however the mock data set includes all scenarios of how users are related to functions.
User Function Sample.pbix
User Function sample.xlsx
I've created some basic visuals that I have in my report (although is missing details from dimension tables that in in my dataset). In the final page I am hoping to relate Users to Functions, even if there are seperate visuals for Users -> Profiles -> Roles -> Functions and Users -> Roles -> Functions, I've been unable to show this relationship in either scenarios.

Please let me know if the link doesn't work, I'm unable to upload files to this forum.

Regards

[MFelix · Accepted solution]

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:

[Mattyt510]

Thank you @MFelix,

The DAX measure you've provided works well for achieving the results I'm looking for currently.

I agree with your point regarding revising the model to avoid filtering relationships both ways, however I'm unsure of what approach should be taken to avoid the issues I'm facing in this case.

Many thanks!

[MFelix]

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.

[Mattyt510]

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.