Relationships with bridge tables

[danielPrieto]

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

[Greg_Deckler]

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

[danielPrieto]

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

[v-danhe-msft]

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

[danielPrieto]

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

[v-danhe-msft]

Hi @danielPrieto,

Based on my test, you could try to modify the relationship between the 'User' table and 'BridgeUserGroup' table:

Result:

Regards,

Daniel He

[danielPrieto]

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:

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

[danielPrieto]

Any idea for this problem?

[Anonymous]

You can Edit interactions in Power BI Desktop and make the client and group slicers not filter each other

[danielPrieto]

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