Forum Discussion
Duplicates values within 1 column
Hello
I am filtering through a table full of sales orders. Over 100,00 rows. I just want to find the sales order numbers that show up more than once in the column and I want to create a column that tells me if there is, or isn't. True/False if you will. I just need to know which sales order#'s are dupicated so i can sort through them for the different PO #'s. Below is the example of what I am looking for.
Agency order# PO# Measure/column
AAA #230 XY True
AAA #230 X True
AAA #230 MM True
BB #165 L False
BB #166 T False
Hi AA622,
You can try to use the following calculate column code, I add conditions to check both two fields to return true/false tags:
Tag = VAR rowcount = COUNTROWS ( FILTER ( 'Table', [Agency] = EARLIER ( 'Table'[Agency] ) && [Order#] = EARLIER ( 'Table'[Order#] ) ) ) RETURN IF ( rowcount > 1, TRUE (), FALSE () )Regards,
Xiaoxin Sheng
3 Replies
AA622 ,
new column =
var _cnt = countx(filter(Table, [Order#] =earlier([Order#]) ), [Order #])
return
if(_cnt >1, true(), false())
Hello
Thanks so much it works but I have a followup question. I sorted the report to only show the 'True' values but I noticed there are still some order#'s that only show up once yet have the 'true' value. Why is that? I wanted it to show only order#'s that are duplicated within the same column
Is there a workaround to this?
Hi AA622,
You can try to use the following calculate column code, I add conditions to check both two fields to return true/false tags:
Tag = VAR rowcount = COUNTROWS ( FILTER ( 'Table', [Agency] = EARLIER ( 'Table'[Agency] ) && [Order#] = EARLIER ( 'Table'[Order#] ) ) ) RETURN IF ( rowcount > 1, TRUE (), FALSE () )Regards,
Xiaoxin Sheng
