ranking duplicates

[Sascha]

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

[Baskar]

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 

[Sascha]

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

[ImkeF · Accepted solution]

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)

[v-huizhn-msft]

Hi @Sascha,

Thanks for the points, please mark the corresponding reply as answer for help more people.


Best Regards,
Angelia 

[Baskar]

Desired Rank 

 is already existing column or calculated column ?

[Sascha]

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

[ImkeF]

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/

[Sascha]

Yes! That's the type of output I'm looking for. Isn't it possible with Dax ?

[ImkeF]

Baskars solution is a DAX-version. I'm not aware of a possibility to create the Index-column in DAX.