cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danielPrieto
Advocate I
Advocate I

Relationships with bridge tables

Hi,

 

I have a data model like this:

 

bridgeTablesRelationShips.PNG

 

 

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 9
Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @Greg_Deckler

 

I know that function USERELATIONSHIP can help but I don't know how in this case. 

 

Suppose the following tables:

 

User:

 

UserIDUsernameClientID
1User_11
2User_21
3User_31
4User_41
5User_51
6User_62
7User_72
8User_82

 

Group:

GroupIDGroupNameClientID
1Group_11
2Group_22
3Group_32

 

BridgeUserGroup:

UserIDGroupID
11
21
72
83

 

Client:

ClientIDClienName
1Client_1
2Client_2

 

UserEvaluation:

UserIDCourseIDScore
112
126
135
228
239
316
326
413
427
516
528
532
617
622
638
719
727
732
816
829
836

 

As you can see we have several users who are not in any group. So I want a report like this


bridgeTables1.PNG

But if I filter by Client1 I can see the following:

bridgeTables2.PNG

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:

1.PNG

Result:

2.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

GroupIDGroupNameClientID
1Group_11
2Group_22
3Group_32
4Group_42

 

And Gorup_4 has no users:

bridgeTables3.PNG

In this case, If I select Client2, Group_4 dissapear:

bridgeTables4.PNG

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:

1.PNG

Result:

2.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-danhe-msft

 

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:

 

bridgeTables5.PNG

 

In summary, I need that when filtering a client:

  • Your users are filtered.
  • Your groups are filtered and, therefore, you can then filter the users of these client by one of their groups.

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 @SPG

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

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.