Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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.
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 |
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] )
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.
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
Yes Zubair, I applied ASC, It worked awesome, Thanks for your support / help.
Regards,
Murali
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 )
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
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.
@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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |