Count duplicate values and number them as 1,2,3.

[Anonymous]

Can any one help me for DAX formula to number the duplicate values as 1,2,3... in the 2nd column

I have tried with this DAX formula in a new column {Double = IF(CALCULATE(COUNT('Rejection'[URN]),FILTER('Rejection','Rejection'[URN]=EARLIER('Rejection'[URN])))>1,1,0)}, but the result is not matched what i have expected.

URN-         Duplicate Numbering
---------------------------
1007171-          1
1007172-          1
1007172-          2
1007172-          3
1007175-          1
1007175-          2
1007177-          1

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

Please see the attached file

We need an assitant field/ column to do this numbering.

So first  add a calculated column

SupportingColumn =
RANDBETWEEN ( 1, 100 )

Then we can use this formula to get desired Duplicate Numbering

Duplicate_Numbering =
RANKX (
    FILTER ( Table1, Table1[URN] = EARLIER ( Table1[URN] ) ),
    Table1[SupportingColumn]
)

[v-jiascu-msft]

Hi @Anonymous,

1. Add an index column in the Query Editor.

2. Add a calculated column with this formula.

Column =
CALCULATE ( COUNT ( Table1[URN-] ), Table1[Index] <= EARLIER ( Table1[Index] ) )

You can try it in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgUNVSbJ_1nd1ZAVS.

Best Regards!

Dale

[Anonymous]

Hi Dale,

Thanks for your help, I tried your way but the value as 1 for the all the URN. Screen shot attached.

Kindly advice if I am wrong.

[v-jiascu-msft]

Hi @Anonymous,

Can you share your pbix file? A dummy one is enough. 

Best Regards!

Dale

[Anonymous]

Hi Dale,

This is the URN, Pls insert it in your PBi file.

URN
850105758
850105766
850105766
850105779
850105779
850105795
850105813
850105813
850105813
850105814
850105824
850105831
850105846
850105863
850105865
850105889
700526874
700526878
700526884
700526888
800248419
850091734
850091754
850091754
850091756
850091765
850102104
850102107
850102108
850102111
275031583
275031598
275031598
275031640
275031643
275031673
275031721
275031721
275031786
275031848
275031860
275031860
275031863
275031906
700519284
700519285
700519289
700519289
700519310
700519313
700519314
700519314
700519318
700519333
700519335
700519335
700519335
700519335
700519338
700519354
700519354
700519356
700519356
700519356
700519356
700519356
700519357
700519358
700519360
700519363
700519364
700519364
700519365
700519365
700519365
700519367
700519371
700519373
700519386
700519389
700519390
700519391
700519392
700519393
700519393
700519414
700519414
700519415
700519415
700519416
700519417
700519417
700519418
700519419
700519421
700519421
700519426
700519428
700519430
700519432
700519433
700519434
700519435
700519435
700519435
700519436
700519437
700519438
700519446
700519447
700519447
700519448
700519450
700519458
700519459
700519459
700519460
700519461
700519468
700519471
700519477
700519477
700519488
700519518
700519545
700519547
700519550
700519555
700519556
700519560
700519569
700519570
700519570
700519571
700519572
700519576
700519577
700519578
700519581
700519582
700519582
700519583
700519584
700519586
700519587
700519587
700519599
700519600
700519600
700519600
700519600
700519601
700519601
700519602
700519604
700519605
700519615
700519621
700519621
700519624
700519626
700519628
700519629
700519631
700519632
700519651
700519654
700519660
700519661
700519662
700519676
700519677
700519678
700519680
700519682
700519685
700519689
700519690
700519691
700519696
700519697
700519702
700519704
700519704
700519704
700519704
700519723
700519734
700519735
700519735
700519738
700519739
700519739
700519740
700519751
700519753
700519772
700519775
700519807
700519813
700519823
700519823
700519848
700519849
700519849
700519869
700519872
700519873
700519887
700519889
700519890
700519891
700519891
700519898
700519900
700519904
700519922
700519922
700519923
700519930
700519932
700519937
700519957
700519959
700519964
700528868
700528871
700528875
700528875
700528876
700528876
700528877
700528896
700528897
700528897
700528899
700528902
700528903
750140360
750140360

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

Please see the attached file

We need an assitant field/ column to do this numbering.

So first  add a calculated column

SupportingColumn =
RANDBETWEEN ( 1, 100 )

Then we can use this formula to get desired Duplicate Numbering

Duplicate_Numbering =
RANKX (
    FILTER ( Table1, Table1[URN] = EARLIER ( Table1[URN] ) ),
    Table1[SupportingColumn]
)

[Anonymous]

Thanks for sharing resolution with sequence from 1 to 3 in finding duplicate records.

If we want to change the order from 3 to 1 then what should be done.

Pls support.

[Anonymous]

Hi Zubair,

Thanks for your help, I just tweeked

SupportingColumn =
RANDBETWEEN ( 1, count(Table[URN] )

Then I applied your RankX . It worked..

Duplicate_Numbering =
RANKX (
    FILTER ( Table1, Table1[URN] = EARLIER ( Table1[URN] ) ),
    Table1[SupportingColumn]
)

Thanks Zubair for your help.

Regards,

Murali

[Anonymous]

Yes Zubair, I applied ASC, It worked awesome, Thanks for your support / help.

Regards,

Murali

[Zubair_Muhammad]

Hi @Anonymous

My Code is numbering them 3, 2,1 instead of 1,2,3 just revise the code below

Duplicate_Numbering =
RANKX (
    FILTER ( Table1, Table1[URN] = EARLIER ( Table1[URN] ) ),
    Table1[SupportingColumn],
    ,
    ASC
)

[Anonymous]

Hi Muhammad!! I saw your answer, even I`m in a different problem, it helped me parcially. I`ll try to be short, and if you need i can send you the image, because I don`t know how to paste the image of the issue right here. I used your furmula: Duplicate_Numbering = RANKX ( FILTER ( Table1, Table1[URN] = EARLIER ( Table1[URN] ) ), Table1[SupportingColumn]) This formula gave number to my duplicates as I wish, however it didn't numerate them by date. I have several duplicates in the same or different dates that I need to give number order by the old date to the most recent. Did you get it? Could you please help me xD If you need I can send the image anywhere or by e-mail, I think for you will be somehow simple. Thanks in Advance 🙂

[Zubair_Muhammad]

Hi @Anonymous

Looks doable

Please could you copy paste some raw data and expected results directly here in the post

Its easy to copy and then find a solution.

You can copy paste Excel cells directly here

[Anonymous]

Finally I was able to look what was wrong, my google chrome wasn't displaying these functions of image paste for me. Sorry for the Mess xD

So, as you can see, I have the duplicate column perfect matching with purchase document, however it is not ordered by the old data to the most recent and I really need this to be ordered, because of the stuff that I have to do after using this order.

[Zubair_Muhammad · Accepted solution]

@Anonymous

[Anonymous]

Hi Zubair,

Thanks for your help on this.

The requirement is if the URN has no duplicates then it should be mentioned as 1 in B next Column, if the URN is getting repeated then it should show as 1,2,3 till the count of Repated URN (Refer URN 100716850 has count 3 and in next column it has 1,2,3..)

URN	Unique / Duplicate Numbering
100716836	1
100716837	1
100716838	1
100716850	1
100716850	2
100716850	3
100716858	1
100716860	1
100716860	2
100716862	1

Regards,

Murali