Forum Discussion
Advocate IRelationships with bridge tables
Hi,
I have a data model like this:
I have a Fact Table (UserEvaluation) and some dimensions (User, Group and Client). The relation between Group and User y m:n so we also have a bridge table (BridgeUserGroup).
I want to make a report with filters by Clients and Groups. So, If I select a Client I want to see all their groups and all their users (whether or not they are in a group). I would like to have the possibility of filter one group to see their evaluation results. The problem is that if there are users that do not belong to any group they will never appear. This occurs because I can't be other active relationship between client and users.
If it is not clear, I can provide an example or share a .pbix.
Does anyone know how to model this type of situation?
Regards,
Dani
9 Replies
- Greg_Deckler
Community Champion
You can get the related users using USERELATIONSHIP. Sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
danielPrietoThanks Greg_Deckler
I know that function USERELATIONSHIP can help but I don't know how in this case.
Suppose the following tables:
User:
UserID Username ClientID 1 User_1 1 2 User_2 1 3 User_3 1 4 User_4 1 5 User_5 1 6 User_6 2 7 User_7 2 8 User_8 2 Group:
GroupID GroupName ClientID 1 Group_1 1 2 Group_2 2 3 Group_3 2 BridgeUserGroup:
UserID GroupID 1 1 2 1 7 2 8 3 Client:
ClientID ClienName 1 Client_1 2 Client_2 UserEvaluation:
UserID CourseID Score 1 1 2 1 2 6 1 3 5 2 2 8 2 3 9 3 1 6 3 2 6 4 1 3 4 2 7 5 1 6 5 2 8 5 3 2 6 1 7 6 2 2 6 3 8 7 1 9 7 2 7 7 3 2 8 1 6 8 2 9 8 3 6 As you can see we have several users who are not in any group. So I want a report like this
But if I filter by Client1 I can see the following:
I can see the users that belong to a group but I would like to see all the user's results of this client, unless I select the group in the corresponding filter.
Can you explain me how use the USERELATIONSHIP function in this case?
You can dowload a .pbix in this link.
Thanks in advance,
Dani
v-danhe-msftMicrosoft Employee
Hi danielPrieto,
From your description, I could not figure out the setence "I would like to see all the user's results of this client", could you mean the user's result as the related username? If so, you could modify your relationship as this:
Result:
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/jj43sepa8z951ui/Relationships%20with%20bridge%20tables.pbix?dl=0
Regards,
Daniel He
