Forum Discussion
Creating a filter table to avoid having two onscreen filters - Ambiguous relationship
Hi there,
In my current report I'm looking to filter 2 tables using one onscreen filter by introducing a helper table (user account details, and required training) by the same column type (team). The issue I'm getting stuck at is Power BI won't allow an active relationship due to an ambiguous relationship between the Team Filter and Training records. Maybe there is a better way to achieve this, or maybe I've incorrect setup my data model.
I'm sure there's something I'm doing wrong, any help would be greatly appreciated, thanks in advance!
Training Records - Fact Table
User account details - Dimension Table
Required Training - Dimension table
Team filter - Helper
Sample Data:
User account details:
| User ID | Team |
| 1 | Team 1 |
| 2 | Team 2 |
| 3 | Team 3 |
| 4 | Team 1 |
| 5 | Team 2 |
| 6 | Team 3 |
| 7 | Team 1 |
| 8 | Team 2 |
| 9 | Team 3 |
| 9 | Team 3 |
| 8 | Team 4 |
Training Records
| Training Title | Training Date | User ID |
| Course 1 | 01/01/2000 | 1 |
| Course 2 | 02/01/2000 | 1 |
| Course 1 | 03/01/2000 | 2 |
| Course 2 | 04/01/2000 | 2 |
| Course 1 | 04/03/2000 | 3 |
| Course 2 | 05/03/2000 | 3 |
| Course 1 | 06/03/2000 | 4 |
| Course 2 | 07/03/2000 | 4 |
| Course 1 | 08/03/2000 | 4 |
| Course 1 | 09/03/2000 | 5 |
| Course 1 | 10/03/2000 | 6 |
| Course 1 | 11/03/2000 | 7 |
| Course 1 | 12/03/2000 | 8 |
| Course 1 | 13/03/2000 | 2 |
| Course 2 | 14/03/2000 | 4 |
| Course 2 | 15/03/2000 | 6 |
| Course 2 | 16/03/2000 | 1 |
| Course 4 | 11/03/2000 | 3 |
| Course 4 | 12/03/2000 | 5 |
| Course 3 | 13/03/2000 | 1 |
| Course 3 | 06/03/2000 | 8 |
| Course 3 | 07/03/2000 | 1 |
| Course 3 | 08/03/2000 | 4 |
Team Filter
| Team |
| Team 1 |
| Team 2 |
| Team 3 |
Required Training
| Training Title | Required By |
| Course 1 | Team 1 |
| Course 2 | Team 1 |
| Course 3 | Team 1 |
| Course 1 | Team 2 |
| Course 2 | Team 2 |
| Course 3 | Team 2 |
| Course 4 | Team 2 |
| Course 1 | Team3 |
| Course 4 | Team 3 |
Kind regards,
Zak
- Anonymous3 years ago
Hi ZakJaconelli ,
You can refer the following links to try to resolve the ambiguous relationship problem.
Resolve relationship path ambiguity
Bi-directional relationships can introduce multiple, and therefore ambiguous, filter propagation paths between model tables. When evaluating ambiguity, Power BI chooses the filter propagation path according to its priority and weight.
Solved: Table relationship ambiguity - Microsoft Fabric Community
Understanding Ambiguity in Power Data Model - Microsoft Fabric Community
How to manage ambiguous relationship in Power BI – Cloud BI
Best Regards
2 Replies
- AnonymousNot applicable
Hi ZakJaconelli ,
You can refer the following links to try to resolve the ambiguous relationship problem.
Resolve relationship path ambiguity
Bi-directional relationships can introduce multiple, and therefore ambiguous, filter propagation paths between model tables. When evaluating ambiguity, Power BI chooses the filter propagation path according to its priority and weight.
Solved: Table relationship ambiguity - Microsoft Fabric Community
Understanding Ambiguity in Power Data Model - Microsoft Fabric Community
How to manage ambiguous relationship in Power BI – Cloud BI
Best Regards
- ZakJaconelliFrequent Visitor
Thank you, this is really helpful to understand how relationships interfact 🙂