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?