Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to 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)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Sascha,
Thanks for the points, please mark the corresponding reply as answer for help more people.
Best Regards,
Angelia
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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Yes! That's the type of output I'm looking for. Isn't it possible with Dax ?
Baskars solution is a DAX-version. I'm not aware of a possibility to create the Index-column in DAX.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |