Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |