Duplicate values are detected from data source. If it's a DirectQuery model, please make sure data

[Klaudia_00]

Together with my team, we have a problem with a report which is in a mixed mode (1 import table, other tables in Direct Query). It has been working for now more than a year. Yesterday, we encountered following error:

This report works correctly on the desktop version of PBI (we used similar filters). We checked that we have no duplicates on the database.

Have you encountered such an error? Do you have any advice?

Thanks in advance!

[plxtime · Accepted solution]

For anyone who has previously posted to this thread, an update from my side. I submitted a support case to the product team the day this issue started happening. I received an update from them today (after sending them reproducible steps to get the issue) that the issue is fixed. And I confirm for me that my impacted reports are working again. Thanks to the PBI product team!!

[Klaudia_00]

Thanks a lot. It's working on our side as well.

[Anonymous]

Hello @plxtime ,

Thank you for the update. The errored report is now working in my case too. Have you been able to identify the cause and solution from your communication with Microsoft from the support ticket?

Thanks.

[plxtime]

I did not get into any details with them. It was definitely a bug on their side though that they had to fix. It seemed to be caused by a specific scenario as well (direct query tables, two many:one relationships with filter direction going from the many side to the one side). But whatever cause and solution it was is something that's probably only known to the PBI development team.

[AnasPBI]

Correction : We have applied the Collate function with insensitivity specification ('en-ci') on the Text columns in our table. 

this is an upgrade on the app power bi that seems to correct the sorting issues when mixing case sensitivity and insensitivity in the same table.

AnasPBI

[v-heq-msft]

Hi @Klaudia_00 ,

We tried to reproduce the problem under the version(2.123.742.0 64-bit (November 2023)& 2.124.1052.0 64-bit (December 2023)) , but we were unable to reproduce it. Here are some steps we have tried:
1. We have used import mode and direct query mode to get data and generate visualiztion in powerbi desktop.

2.Three of the tables in the data obtained by the direct query have many-to-one and many-to-many relationships, one table with duplicate values and two tables with non-duplicate values.

3.After pubish to the service and refresh on-demand, the report displayed correctly.

To troubleshoot the problem further, please provide us with more information such as relationships between your tables, data source types and files to test .

Please make sure not attach sensitive data before sharing the files.

Best Regards,

Albert He

[Anonymous]

Hello everyone,

We've encountered same challenge with semantic models using direct query mode to AWS Snowflake. Please take a look at the issue and consider voting for it:

Link to the Issue

[AnasPBI]

Hello @everyone , 

As i have already shared with you, we have encountered the same issue, and managed to correct it.

Correction : We have applied the Collate function with insensitivity specification ('en-ci') on the Text columns in our table. 

this is an upgrade on the app power bi that seems to correct the sorting issues when mixing case sensitivity and insensitivity in the same table.

Please let me know if it works for everyone.

AnasPBI

[TSHECKEL]

This appeared for us yesterday as well. The report had been working fine for quite some time and now this message popped up. No issues on the desktop, only on the service. 

[TSHECKEL]

I followed @liz_wade_rmit's discovery and changed a 1:N Cross Filter Direction of single to both and this resolved the issue. It's not ideal but at least the report is rending out on the service again.

[ChaoticBurst]

Started getting this error a few days ago also in the web version as well.  There are no duplicates in the datasource, and the desktop version works without error.  Web version had worked fine without error on the same dataset previously.

[plxtime]

Same issue is happening for us. What's stranger is I had an end user download PBI desktop when they reported the issue, because it was working in my desktop app, but in their desktop app (I'm assuming latest version) they had the same error. But, it works perfectly fine in my desktop app. It must be a client update causing the issue.

[liz_wade_rmit]

I actually found a solution yesterday. There was no duplicity when I checked the data and it was working on Desktop and the strange part is that it is historic data which has not changed in a year and the report is being used on Web for that long. Anyway, the fix I found was to remove a relationship inside the Data Model. I had a 1 to many where the "many"side was going to another 1 to many. And that made the report work again. Although I would still be interest to know why now the error. It seems a new version of Power BI extra check... And my report is only using Direct Query.

[Klaudia_00]

Thank you for sharing. We actually have a simple schema with one to many relationships and one way filtering. For one table we have filtering in both directions - it didn't help when we changed it to one way.

[liz_wade_rmit]

Someone from my team also suggested changing the relationship to "many to many". I know it sounds ilogical if your relationship is 1 to many, but maybe worth a try.

[aligator123]

I faced the same issue. The report was working fine previously. I changed data storage type for a small table to Dual from Import and it started working again. Not sure if this has to do something with PBI ubdate.

[Klaudia_00]

Thanks for sharing. I was wondering how did you manage to switch the table from Import to Dual mode?

[AnasPBI]

Hello , 

We are facing the same problem : ""Couldn't load the data for this visual

Duplicate values are detected from data source. If it's a DirectQuery model, please make sure data source collation is consistent with this Analysis Services model.""

We checked the data and there's no duplicates in the source , table is working fine in the desktop version. 

Please post when you find an answer or solution to this . 

Thank you .

[boborambow2]

Yup, same here. Just stopped working on the web version recently. I have a mixed mode report just like yours, 1 import and other tables in Direct Query. Mine work correctly on the desktop version as well.

[boborambow2]

I wasn't able to fix the report using the existing tables. Originally my report was setup to just connect to the tables and then build relationships. Then build the report from those tables. 

The only way I was able to fix it was to abandon that completely and write a custom SQL query for the report I was making. Once I did that, the report is able to run just fine. It will just be annoying when a change is requested, I'll need to edit the qeuery instead of just adding a field from the tables.