Forum Discussion

Radhika2605's avatar
Radhika2605
Icon for Helper II rankHelper II
5 years ago
Solved

Need help with measure

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- 

Top N Atheletes with maximum medals =
CALCULATE(
[Total Medals],
FILTER( VALUES( Athlete[Name] ),
RANKX( ALL( Athlete[Name] ), [Total Medals], , DESC ) <= 10 )
 
Here are the results-

 

 

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

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Radhika2605 

    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.

9 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    Radhika2605 

    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.

    • Radhika2605's avatar
      Radhika2605
      Icon for Helper II rankHelper II
      5 years ago

      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

  • Radhika2605's avatar
    Radhika2605
    Icon for Helper II rankHelper II
    5 years ago

    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

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      5 years ago

      Radhika2605 

      i did a test. maybe you can create a new table and create the visual based on the new table.

      Table = TOPN(10,'Table',RANKX(all('Table'[name]),rankx(ALL('Table'[name]),[_sum],,DESC)+RANKX(all('Table'[name]),[_name],,ASC)/10,,ASC))