Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
masplin
Impactful Individual
Impactful Individual

Duplication issue in inherited model due way relationships are operating

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

Capture.PNG

 

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

Capture1.PNG

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

 

 

 

2 REPLIES 2
amitchandak
Super User
Super User

 If possible please share a sample pbix file after removing sensitive information.
Thanks

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
masplin
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.