Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
@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