Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Everyone,
I am working on an olympics data set and I am trying to find the top 10 atheletes using dax measure but instead of giving 10 results it is returning more than 10 results.
Dax measure used-
Is it because the total number of medals has duplicate values or there is something wrong in my logic. Could you please guide me as to how can I get exactly 10 values even when total number of medals won by atheletes is duplicated.
My model is -
Thanks
Radhika
Solved! Go to Solution.
that depends on how you want to choose the top 10 when total medals is dpulicated.
let's say by name.
_name=max( Athlete[Name] )
_rank=RANKX( ALL( Athlete[Name] ), [Total Medals], , DESC ) +RANKX( ALL( Athlete[Name] ), _name,DESC)/10
then you will get no duplicated rank.
Proud to be a Super User!
it looks you have hundreds of names. pls try to change /10 to /100 or /1000
Proud to be a Super User!
Hi Ryan,
One last thing on this one - when I am trying to create a stacked bar chart for these top 10 athletes and trying to bring the medal type(gold, silver and bronze) in the legend field then instead of showing 10 values it shows a lot of athletes. Left side is the measure we just created and right side is all the athelets and their medals. Even the distribution of medals shown in our meausre looks to be incorrect as it is not showing the bronze measures. This only happens when I bring medal type in legend.
What should I do for this?
Thanks
Radhika
i did a test. maybe you can create a new table and create the visual based on the new table.
Proud to be a Super User!
that depends on how you want to choose the top 10 when total medals is dpulicated.
let's say by name.
_name=max( Athlete[Name] )
_rank=RANKX( ALL( Athlete[Name] ), [Total Medals], , DESC ) +RANKX( ALL( Athlete[Name] ), _name,DESC)/10
then you will get no duplicated rank.
Proud to be a Super User!
Hi @ryan_mayu
Thank you so much for your prompt response.
Could you please explain the logic you used a bit as I am unable to comrehend it (sorry still at beginner). Also, I tried to use what you suggested but it is giving error as shown in the screenshot below:
Thanks
Radhika
here is a workaround for you. pls see the attachment below
Proud to be a Super User!
Hi @ryan_mayu,
Thanks for you effor in sending the solution. I did tried it but the issue is it is still not giving the correct result. the result which I am getting is on the left.
Right hand table is the table which shows the total number of medals won and I have arrange it in descending order according to the number of medals. It shows that the highest medal won is 58, then 39 and then 38.
But the tabel on the left only has values for athelete names starting from alphabet A. I think it is arranging it in alphabetical order or something.
how should the solution which you have provided needs to be refined for it to work properly?
Thanks in advance
Radhika
it looks you have hundreds of names. pls try to change /10 to /100 or /1000
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
85 | |
67 | |
49 |
User | Count |
---|---|
135 | |
112 | |
100 | |
66 | |
62 |