cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MuraliMani Regular Visitor
Regular Visitor

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

Hi @MuraliMani

 

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]
)
Try my new Power BI game Cross the River

View solution in original post

Super User
Super User

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

13 REPLIES 13
v-jiascu-msft Super Contributor
Super Contributor

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

Hi @MuraliMani,

 

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.Count duplicate values and number them as 1,2,3..JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MuraliMani Regular Visitor
Regular Visitor

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

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.

 

Test.png

v-jiascu-msft Super Contributor
Super Contributor

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

Hi @MuraliMani,

 

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

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MuraliMani Regular Visitor
Regular Visitor

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

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
Super User
Super User

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

Hi @MuraliMani

 

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]
)
Try my new Power BI game Cross the River

View solution in original post

MuraliMani Regular Visitor
Regular Visitor

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

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..)

 

URNUnique / Duplicate Numbering
1007168361
1007168371
1007168381
1007168501
1007168502
1007168503
1007168581
1007168601
1007168602
1007168621

 

Regards,

Murali

Super User
Super User

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

Hi @MuraliMani

 

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
)

 

 

Try my new Power BI game Cross the River
Super User
Super User

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

MuraliMani Regular Visitor
Regular Visitor

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

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 245 members 2,778 guests
Please welcome our newest community members: