Forum Discussion

TooOld's avatar
TooOld
Frequent Visitor
4 years ago
Solved

RANKXX problem

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, but didn't find a solution

Rankx with filter
  • TooOld's avatar
    TooOld
    4 years ago

    Hello Icy

    my formula didn't work on further subfilter like , Gender and Country/Nation. The correct solution is

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

    I thank you for contribution. I still work on further ranking results, like 
    Average by 6 best results in a month for each user

    Best Chris

4 Replies

    • TooOld's avatar
      TooOld
      Frequent Visitor
      4 years ago

      At time I got the result

      Weekly RankUserNameGenderAgeNationActyVoltsSubfilter
      1JermsM36IT78961
      2AjasF33CH67062
      3ArlindF27CH56973
      4SeppM57CH32006
      5SoldoF40DE2459


      The Column Weekly Rank represents the overall ranking of the username based on column ActyVolts. Weekly Rank = Overall Ranking (Done in SQL Server)

      The Column Subfilter shall show different rankings based on slicer and not change the Weekly Ranking
      - Nation
      - Gender


      No Gender or Nation filter from slicer set - expected

      Weekly RankUserNameGenderAgeNationActyVoltsSubfilter
      1JermsM36IT78961
      2AjasF33CH67062
      3ArlindF27CH56973
      4SeppM57CH32004
      5SoldoF40DE2455

       

      Gender set to Women Only (Slicer Gender set to F))

      Weekly RankUserNameGenderAgeNationActyVoltsSubfilter Nation = F (Female)
      2AjasF33CH67061
      3ArlindF27CH56972
      5SoldoF40DE2453

       

      Nation set to 'CH' (Slicer Nation)

      Weekly RankUserNameGenderAgeNationActyVoltsSubfilter Nation = CH (Slicer)
      2AjasF33CH67061
      3ArlindF27CH56972
      4SeppM57CH32003

       

      To sum up
      In Sports there is an overall ranking for a contest, but participants expects to see a filtered ranking
      - Women
      - Nation
      - AgeGroup

      I thank you for helping here out

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi TooOld ,

     

    Based on my test, your measure works. Do you mean you want to modify the expression? If so, try this:

    mRankOnFilterActyVolts 2 =
IF (
    HASONEVALUE ( RfvRefined[TotalCalculatedPower] ),
    RANKX (
        ALLSELECTED ( RfvRefined ),
        CALCULATE ( MAX ( RfvRefined[TotalCalculatedPower] ) )
    )
)

    In addition, if you just want to show the top1 value in the table visual, you can set filter like so:

     

     

     

    Best Regards,

    Icey

     

    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 Icy

      my formula didn't work on further subfilter like , Gender and Country/Nation. The correct solution is

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

      I thank you for contribution. I still work on further ranking results, like 
      Average by 6 best results in a month for each user

      Best Chris