How to use 3 different table relationship in Power BI?

Question

I am trying to implement a donut chart in Power BI using 3 different tables. There are two tables users and groups with their data and there's one group_users table for storing user's group activity. group_users table has foreign key for both the tables.

Now I want to create a visualization which can easily convey information from 3 of the tables into one. Unlike Tableau, I am not able to have multiple joins on single table as it gives relationship ambiguity issue. A Diagram of it is as shown below:

When I try to create group_users' and users' relationship active, it gives me "You can't create a direct active relationship between group_users and groups because that would introduce ambiguity between tables users and group_users. To make this relationship active, deactivate or delete one of the relationships between users and group_users first." as an error. Note, the connection is established to the Redshift cluster.

The donut chart should contain each group's count of users seperated by their age of users in different colors. And I should be able to filter each group's donut chart.

Please let me know, how should I create such visualization in Power BI?

Answer 1

First deactivate the relationship between users and groups, because that is where the ambiguity is coming from. When you have done that, you can activate the relationship from group_members to groups without an issue.

in the visualization, put the group name in the values, and the age in the detail (make sure you change the age to a text data type so that it appears as categories that are counted, rather than a value to summarized)

Answer 2

It's hard to say exactly without knowing foreign keys in users table for groups and group_members table.

Nonetheless, based on what I understand you want to achieve, you should connect groups table to group_members table on group_id (so that when you click on specific group_name in groups table it filters for members of specific group in group_members table). Then you need to connect group_members table to users table on some sort of account_id that I assume you have in both tables. Than right click on the relationship arrow between users and group_members, select preferences and change Cross filter direction to Both
Set cross filter direction

This should do the trick and give you the relationships that you need. You don't need relationship between users and groups tables.

Answer 3

Make sure that there is a relationship between all 3 of the tables.

1. Go to Model View (hi-lighted on left)
2. Check that there are joining fields between the tables (examples hi-lighted)
3. If there are joining fields, with no active join (denoted by the lines between the tables), drag the relevant field from table 1 over the relevant field in table 2 (same then for tables 2 and 3)
4. Return to the Report view. Field s from each table should be available for inclusion in the donut visual