Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
Thanks @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
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
Hi @v-danhe-msft,
When I say "user's results" I mean "user's evaluation" but getting the username from the UserEvaluation table is enough. However, with your approach I would have another similar problem:
If I have a group that has not users that group would not appear in the corresponding filter:
Suppose the same case but now Group table is:
GroupID | GroupName | ClientID |
1 | Group_1 | 1 |
2 | Group_2 | 2 |
3 | Group_3 | 2 |
4 | Group_4 | 2 |
And Gorup_4 has no users:
In this case, If I select Client2, Group_4 dissapear:
I would like to be able to filter a client and see all their groups (with or without users) but if I don't select any group I would like to see all user's evaluation (whether or not they are in a group). You can download de .pbix here.
Is there any possibility to do this?
Regards,
Daniel
Hi @danielPrieto,
Based on my test, you could try to modify the relationship between the 'User' table and 'BridgeUserGroup' table:
Result:
Regards,
Daniel He
As I said before, this is not a real possibility. In the real case, we have hundreds of clients and thousands of groups so when I filter a client I also need to see ONLY their groups. In your proposal when filtering by Client_2 all the groups appear but Group_1 belongs to Client_1 so it should not be displayed.
In addition, the relationship between User and BridgeUserTable should be in the opposite direction since a user could belong to several groups, that is:
In summary, I need that when filtering a client:
Regards,
Daniel
Any idea for this problem?
You can Edit interactions in Power BI Desktop and make the client and group slicers not filter each other
Hi @Anonymous,
thanks for your suggestion but this is not a real possibility. Here I am showing a sample of data but in the real data I have many clients and thousands of groups so I need that if I filter by client, only their groups appear.
Regards,
Daniel
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |