Forum Discussion
Highlight duplicate values in a table
Hello Everybody, I need some help to highlight duplicate values in a table. I tried may offered solutions but I don't get it working, the measures and calculated columns (eg the offered and found solutions) are working but they don't work in a filtered table.
So the issue, I have a dataset with about 1.4 millions rows and several columns. The column value contains text and values, this is also the column whats it is al about. Cleaned the dataset, make some changes and it works great. When in a table, I need to address the duplicate values, but the solutions I tried keeps filtering the whole dataset instead of only the table. Also tried visual calculations, bu not found the solution yet.
Any one here a suggestions? If you need more information etc. just let me know.
Thanks in advancedđź‘Ť
Hi HVAC_Specialist ,
Yes, this is now correct. The main difference from your previous result is that the Value column is no longer aggregated, so the duplicate check occurs at the row level as intended. The value 100 is appropriately flagged as a duplicate when it appears multiple times within the current filter context, while other values are not flagged. This matches the expected behavior for a measure-based approach, as it evaluates only the visible rows in the table rather than the entire dataset.
Please find the attached PBIX and Screenshort file for your reference.
Thank you.
8 Replies
Measure to detect duplicates within the filtered table:
Duplicate Flag :=VAR CurrentValue =SELECTEDVALUE ( 'Table'[Value] )VAR CountInContext =CALCULATE (COUNTROWS ( 'Table' ),KEEPFILTERS ( 'Table'[Value] = CurrentValue ))RETURNIF ( CountInContext > 1, 1, 0 )To highlight duplicates:- Select the column in the table visual.
- Open Cell elements or Conditional formatting.
- Choose Format by: Field value.
- Select the Duplicate Flag measure.
- Define formatting rules for output 1 (duplicate) and 0 (non‑duplicate).
This will highlight duplicates only in the context of the filtered table, which calculated columns cannot achieve
The cells are not highlighted, even the measure doesn't show any duplicates. Thanks in advanced
what filter are you using?
could you pls provide some sample data (not the screenshot) and the expected output?
Hi HVAC_Specialist ,
Thank you Zanqueta for sharing your response!
Just checking has your concern been resolved? If the provided answer helped, please confirm so others can benefit from the solution too.
Thank you.Hello Zanqueta, the duplicate numbers are not highlighted.
Hi HVAC_Specialist ,
Yes, this is now correct. The main difference from your previous result is that the Value column is no longer aggregated, so the duplicate check occurs at the row level as intended. The value 100 is appropriately flagged as a duplicate when it appears multiple times within the current filter context, while other values are not flagged. This matches the expected behavior for a measure-based approach, as it evaluates only the visible rows in the table rather than the entire dataset.
Please find the attached PBIX and Screenshort file for your reference.
Thank you.
Hi HVAC_Specialist ,
Please refer the below link from community this may help for your solution.
https://community.fabric.microsoft.com/t5/Desktop/Highlight-Fields-when-duplicate-value-found-in-table/td-p/3192839Please mark this as a solution if this helps!!
Thankyou!
