Ranking issue

[TooOld]

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

pbix File to download 

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

[v-kkf-msft · Accepted solution]

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]

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

[lbendlin]

Buckle up for a wild ride:  Use of RANKX in Power BI measures - SQLBI