Have a table of payments where one vendor made two identical payments on the same date. When I display the data in a table the duplicate doesn't show up. Changing the payment by adding $0.01 and it is displayed. Why?
See attached test results... the $10,750.00 entry is the issue.
2 ACCEPTED SOLUTIONS
Hi,@newhopepdx .I am glad to help you.
The problem you are experiencing is very common and normal in Power BI, as it is related to power BI's logic of dealing with duplicate values, in the data model, Power BI usually treats identical records as duplicates and does not display these duplicates by default, but the duplicates are still involved in the operation normally, i.e., you said that when you take the aggregation, the result is calculated correctly. It's just that the extra duplicates are sometimes not displayed, which can be avoided by adding some markers to make the two identical records different. At this point, they will no longer be considered duplicates
For example, it is common practice to add an index column in the power query editor and give each row of data an ID-like tag.
Here is my test:
This is the original data, you can see that I have a lot of duplicates, and when I put it into vsiual without any processing, according to the principle of auto ignore, you will only see two records.
It is possible to automatically aggregate and display identical records without duplicates, this situation will not affect your calculations, only the display, if you do not want the system to automatically ignore duplicates you can do the following
Add ID columns (sorted columns) so that each record is different from the others, add the sorted columns together to visual.
By adding the index column and choosing not to aggregate, you can ensure that the desired fields are aggregated and that the same rows are displayed.
You can also add an ID column in the data source (such as database) labeling each record so that you don't need to manually create the sorting sequence each time, in short, you just need to make the same record in visual a little different to avoid this situation, and it will not affect the final results of your calculations.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That is indeed unfortunate, but thank you for clarifying the realities.
In this scenario we have a built-in solution... the full database does have a trasaction ID field that, trying to reduce the weight of the semantic model, and not needing it for the report, I removed that column. Adding it back, as you noted, will solve the problem.
Hi,@newhopepdx .I am glad to help you.
The problem you are experiencing is very common and normal in Power BI, as it is related to power BI's logic of dealing with duplicate values, in the data model, Power BI usually treats identical records as duplicates and does not display these duplicates by default, but the duplicates are still involved in the operation normally, i.e., you said that when you take the aggregation, the result is calculated correctly. It's just that the extra duplicates are sometimes not displayed, which can be avoided by adding some markers to make the two identical records different. At this point, they will no longer be considered duplicates
For example, it is common practice to add an index column in the power query editor and give each row of data an ID-like tag.
Here is my test:
This is the original data, you can see that I have a lot of duplicates, and when I put it into vsiual without any processing, according to the principle of auto ignore, you will only see two records.
It is possible to automatically aggregate and display identical records without duplicates, this situation will not affect your calculations, only the display, if you do not want the system to automatically ignore duplicates you can do the following
Add ID columns (sorted columns) so that each record is different from the others, add the sorted columns together to visual.
By adding the index column and choosing not to aggregate, you can ensure that the desired fields are aggregated and that the same rows are displayed.
You can also add an ID column in the data source (such as database) labeling each record so that you don't need to manually create the sorting sequence each time, in short, you just need to make the same record in visual a little different to avoid this situation, and it will not affect the final results of your calculations.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That is indeed unfortunate, but thank you for clarifying the realities.
In this scenario we have a built-in solution... the full database does have a trasaction ID field that, trying to reduce the weight of the semantic model, and not needing it for the report, I removed that column. Adding it back, as you noted, will solve the problem.
If I change the Summarization on the Amount field in the table to "Sum", the both records are added together and the total is correct, but if it is set to "Don't summarize" the second (identical) entry isn't displayed... as shown in the examples.
