cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
digitalAstro
Frequent Visitor

PowerBi duplicating records

Hi all!

I have a problem and I simply cannot find the solution.I have a DirectQuery from Dataverse (Microsoft Dynamics), with a table showing funders and funding to different projects. From the same Dataverse, I have another table with project info (projectID, projectName, etc.). The same founder could be for many projects, and projects can have multiple funders.

My problem started when I created a bar chart, where x-axis is funder and y-axis is the sum of the amount. The problem: For one of the funders, the amount shows up double the amount in the table. We can say this is FunderA - in a bar chart, the funding shows as 10.

To understand what was wrong, I tried to see how many fundID it counted, and it said two. I cannot see two records in the table, but in the linked project table I can see a duplicate record for the project. So I am assuming that this is causing the issue. I don't know how this happened however - I have refreshed it plenty, and it looks correct in the Dataverse. Both records are exact duplicates (down to when they were created and last modified), but as it is a DirectQuery, I can't remove the duplicates (I need the data to be live). I saw you used to be able to enable all functions for DirectQuery, but it seems they removed this feature since then.

Is this something someone has experienced and knows a workaround for?

2 REPLIES 2
lbendlin
Super User
Super User

This would be more of a question for your data model. How does that look like between these two tables?

The two tables are linked on the unique key project ID - many to one.

If we imagine table A:

fundIDfundNameprojectIDprojectNameStateamount
1fundA3projectXActive5
2fundB3projectXInactive2
3fundC4projectYActive10

 

table B (with the duplicate record):

projectIDprojectNamestartDateendDateState
3projectX01/01/2201/06/22Active
4projectY01/04/2201/04/23Active
4projectY01/04/2201/04/23Active

 

The duplicate record does not show in Microsoft Dynamics, so I can't delete it or change state and filter from there.

Edit: For some reason the formatting comes out strange, so here is a screenshot:

digitalAstro_1-1670255764217.png

 

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors