Forum Discussion
ranking duplicates
Hi,
I'm looking for a way to 'rank duplicates'.
Basically in a simplified way I have data per customer for differen periods, and I want to rank data based on a combination of this. I can best explain by example, see the table for the result (column 'Desired Rank') I am looking for.
(I later want to use this to be able to filter to only take the first item per client/period etc).
Hope someone can help me out! Thanks.
| Client no. | Period | Desired Rank |
| 100 | Q1 | 1 |
| 100 | Q2 | 1 |
| 101 | Q1 | 1 |
| 101 | Q1 | 2 |
| 102 | Q1 | 1 |
| 102 | Q2 | 1 |
| 102 | Q2 | 2 |
| 103 | Q1 | 1 |
| 104 | Q1 | 1 |
| 104 | Q1 | 2 |
| 104 | Q1 | 3 |
| 104 | Q2 | 1 |
Good points! Giving me the idea of a nice DAX-only solution, that would work in PP as well (Unique row identifier is essential here) ;-)
RANKX(FILTER(table1; table1[Code]=EARLIER(table1[Code]));[Unique ID];;1)
9 Replies
Cool , I have created one calculated column for this , it is working for me.
But u have to create one Index column on this using Power Query. pls look the attachment
let me know if any help
Nice, good to see it's possible. However I'm still struggeling. I have to do this in Powerpivot and not sure I can use the provided solutions.
I've tried:
Count=calculate(countrows(table1); allexcept(table1; table1[code]))
Rank=RANKX(filter(ALL(table1[Code]);[Client no.]);[Code])
ID Client no. P Code Count Rank Desired Rank
345 100 Q1 100Q1 1 1 1
346 100 Q2 100Q2 1 2 1
347 101 Q1 101Q1 2 3 1
348 101 Q1 101Q1 2 3 2
349 102 Q1 102Q1 1 4 1
350 102 Q2 102Q2 2 5 1
351 102 Q2 102Q2 2 5 2
352 103 Q1 103Q1 1 6 1
353 104 Q1 104Q1 3 7 1
354 104 Q1 104Q1 3 7 2
355 104 Q1 104Q1 3 7 3
356 104 Q2 104Q2 1 8 1
Good points! Giving me the idea of a nice DAX-only solution, that would work in PP as well (Unique row identifier is essential here) ;-)
RANKX(FILTER(table1; table1[Code]=EARLIER(table1[Code]));[Unique ID];;1)
Desired Rank is already existing column or calculated column ?
'Desired Rank' is the outcome I am looking for. So it is not yet an excisiting column in my BI, just created it here to show what I mean.
This looks like a nested index. Have a look how this goes here: https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/
