Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
I have inherited a data model that is duplicating values. Below is a simplified version to explain the issue
Calls table (Call ID unique, multiple values of others)
Call ID
Adviser ID
Client ID
Case Notes Table(ATE ID unique multiple values of others)
Askthe Expert ID
Adviser ID
Client Name
Phone user Table (unique values)
Adviser ID
Name
Team Table (unique values)
Client ID
Client Name
Relationship between Adviser ID on both tables and Phone User Table. Relationship between Calls[Client ID] and Team[Client ID]. cannot also create a relationship case Notes[Client Name] and Team[Client Name] as conflict.
The issue is that on a daily basis an adviser my dela with a calls from more thna one client. The current setup uses a slicer on Team[Client Name]. However what seems to be happening is you pick a Client Name, this then selects all of the advisers in the Calls table who had a call with that client, it then passes this to the Case Notes table to get the numbers of case notes, but may pull rows from a client who is not the same as the original Team selected.
for example for this one advisor on one day they answered these calls
they also dealt with 15 case notes just for Network Rail. The bottom table has Team[Client name] on the column headers and Case Notes[Client name] on the rows, showing the 15 cases being recorded in duplicate under every Team she took a call for that day
The issue is the users wants to be able to slice the data by team and also by user name. Because both fields are on both tables you can't use ralationships to do this, without running into an issue of double counting. I am struggling to see any way to resolve this. Basically you can't pass the Team name through either of the Fact tables. I could just have the Team table related to the client fields on Calls and Case notes which would solve the reporting at client level, but then I can't report at user level.
Any suggestions greatly appreciated
Mike
If possible please share a sample pbix file after removing sensitive information.
Thanks
I'd have to build one as the model is enormous.
However I think I might have solved it. For some reason the guy who built it put the relationships between Team->Calls->Phone User as "Both" ways. I made them one way and then could add the Client relationships to the Team table. This seems to have stopped the duplication i was seeing as now the team slicer is going directly to the relevant rows in the fact tables. i'm still not 100% sure how the "both2 directions works, but know it can have unintedend consequences.
I'm checking now all the rpeorts aspossibly the guy changed the relationship to both later to make something else work, so breaking earlier reports. i'll be back with a simplified modelif all I've done is create a different problem.
Thanks
Mike
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |