Forum Discussion

TooOld's avatar
TooOld
Frequent Visitor
4 years ago
Solved

Ranking issue

Hi

I'm working on a result report on sport activities. Data source is a sql server database.

The report shall show results in a table based on field parameter slicer
Ranking 
- Daily
- Weekly
-Monthly
- Yearly

The overall ranking result is workung well, as I calculate the ranking in sql server queries and those shall be always visible independent on any filter set

Problem
I created a measure 'mRankOnFilterActyVolts', which shall create a sub ranking based on filter 
- Gender
- Nation
- ....

My problem is, that I only want to see the best result from the users based on TotalCalculatedPower. 

Actually I stuck on getting the max value in the context of a week, month, year


Measure

mRankOnFilterActyVolts =
IF(
HASONEVALUE(RfvRefined[TotalCalculatedPower]),
//var maxVolts = MAX(RfvRefined[TotalCalculatedPower])
 
var LookupTable =
ADDCOLUMNS(
ALLSELECTED(RfvRefined),
"@sumVolts", RfvRefined[TotalCalculatedPower]
)
 
var currentValue = SELECTEDVALUE(RfvRefined[TotalCalculatedPower])
var Result =
RANKX (LookupTable, [@sumVolts] , currentValue)
RETURN
Result
)

 

I suspect the 'ALLSELECTED'  but have actually no idea how to solve it




Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi TooOld ,

     

    I am not sure if I understood your question correctly. Are you trying to sort the maximum TotalCalculatedPower value for each user?

     

    _max = 
CALCULATE (
    MAX ( RfvRefined[TotalCalculatedPower] ),
    ALLSELECTED ( RfvRefined ),
    VALUES ( RfvRefined[UserName] )
)
    mRank2 = 
RANKX ( ALLSELECTED ( RfvRefined ), [_max],, DESC, DENSE )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi TooOld ,

     

    I am not sure if I understood your question correctly. Are you trying to sort the maximum TotalCalculatedPower value for each user?

     

    _max = 
CALCULATE (
    MAX ( RfvRefined[TotalCalculatedPower] ),
    ALLSELECTED ( RfvRefined ),
    VALUES ( RfvRefined[UserName] )
)
    mRank2 = 
RANKX ( ALLSELECTED ( RfvRefined ), [_max],, DESC, DENSE )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • TooOld's avatar
      TooOld
      Frequent Visitor
      4 years ago

      Hello v-kkf-msft

      your answer helps a lot. Dynamically it works very well. The only issue open is I'd like to add also the Challenge attribute to the table visual. When I do it, then your solution breaks.
      I have to learn more about filter and context transition