Forum Discussion

DMis's avatar
DMis
Regular Visitor
6 years ago

ALLSELECTED and RANKX not working as expected

I have spent last 10 hours or so trying to rank a single field filtered by some other fields and nothing works. It's driving me crazy, when I see a blog entry listing my solution, copy it and it stil...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

DMis 

If you want to have a dynamic value, try create measures instead of a column. 

 

You need two measures separately, Try something like: 

 

Mark measure = Sum(Marks[Mark])

Rank Mark = RANKX(ALLSELECTED('Marks'),Mark measure,,Dese,Dense)

 

 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

DMis's avatar
DMis
Regular Visitor
6 years ago

Hi Paul,

Done more reading and you seems to be right that I must use measures if I need to have the rank recalculated in runtime based on filtered values selected by user.

 

Following your advice I created two measures against Marks table:

MM = SUM(Marks[Mark])
MarkRankMeasure = RANKX(ALLSELECTED(Marks),[MM],,DESC,Dense)

 Then I added MarkRankMeasure to the report view and had all records multiplied

 

The upside is the rank is calculated properly now. However, what can I do about row multiplication?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    DMis 

    In this case, you would need to rank by another columns (e.g. studentID). So you should have 3 measure in total: 

     

     

    MM = SUM(Marks[Mark])
ID= Sum(Marks([StudentID])

Final Rank = 
RANKX ( 
    ALL ( 'Table' ),
    RANKX ( ALL ( 'Table' ), [MM],, ASC )
        + DIVIDE (
            RANKX ( ALL ( 'Table' ), [ID],, DESC ),
            ( COUNTROWS ( ALL ( 'Table' ) ) + 1 )
        ))

     

     

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • DMis's avatar
      DMis
      Regular Visitor
      6 years ago

      Paul,

      Can you please elaborate on why this is happening in the first place? From the brief look at the new formula you provided, I am not sure what it's supposed to do, to be honest. 

       

      I just want to clarify that in the last screenshot first 20 records is how it supposed to be, but there is an excess produced, which is everything that does not have MM value. If I simply filter out all rows that do not have MM value, I get the desired result.

       

      However, I still don't understand how come RANKX produces additional rows, which never existed in the data set to begin with. I don't expect it to behave as INNER JOIN on something else.