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 Anonymous,
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
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:
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!