Forum Discussion
Helper INeed Help in Repeated value
Need help in calculation to find out the repeated value in a period of 30 days and display the repeated value as 'Repeated' as shown in the sample data. I have to find out whether a TP ID is repeated or not, if TP ID is repeated then, i have to display it as 'Repeated ID' under the third column 'Duplicate/Repeat'.
ID TP ID Duplicate/Repeat
T3746798
T3746794 V171990 Repeat ID
T3746772
T3746771 V160880
T3746755
T3746754
T3746753 V162650
T3746706 V135213 Repeat ID
T3746708 V171990 Repeat ID
T3746695
T3746694 V135213 Repeat ID
Any help in this calculation is appreciated.
Hi pjdsh ,
Try this calculated column.
Column = VAR _COUNT = IF ( [TP ID] <> BLANK (), CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[TP ID] ) ) ) RETURN IF ( _COUNT > 1, "Repeat ID" )You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
hi pjdsh - you can calculate the number of duplicates using the below logic - this is essentially counting the number of records with the value "Repeat ID" in the Duplicate column
Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
pjdshHi Sumanth,
if you look at the data, second column TP ID has some duplicates, whenever there is a duplicate TPID, 'Repeated' is the value i will have to display in the third column 'Duplicate/Repeated' .
hi pjdsh - Ok, so if I am understanding it correctly - you want the count to be calculated off the TP ID column is it?
Sorry was not clear on your last message. Thanks!
- amitchandak
Super User
pjdsh , Create a new column like
if(isblank(countx(filter( Table, [TP ID] = earlier([TP ID]) && not(isblank([TP ID]))),[TP ID])) blank(), "Repeat ID")
- pjdsh
Ok, will try and let you know the result. Thank you for the help
- pjdsh
Hi amitchandak , i am getting data like this after applying the formula, i tried different possible ways to get the issue resolved. But couldnt get the solution. Can you please help.
ID TP ID Duplicate/Repeat
T3746798 Repeat ID
T3746794 V171990 Repeat ID
T3746772 Repeat ID
T3746771 V160880 Repeat ID
T3746755 Repeat ID
T3746754 Repeat ID
T3746753 V162650 Repeat ID
T3746706 V135213 Repeat ID
T3746708 V171990 Repeat ID
T3746695 Repeat ID
T3746694 V135213 Repeat ID
Hi pjdsh ,
Try this calculated column.
Column = VAR _COUNT = IF ( [TP ID] <> BLANK (), CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[TP ID] ) ) ) RETURN IF ( _COUNT > 1, "Repeat ID" )You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
