Forum Discussion
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]),FIL...
Hi Anonymous
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
v-jiascu-msft
Microsoft Employee
Microsoft EmployeeHi 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
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
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