cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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 November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors