ALLSELECTED and RANKX not working as expected

Question

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 still does not work.

Anyhow, I have this model and data:

ModelMarks dataStudent data

MarkRank is a calculated column using this formula:

MarkRank = RANKX(ALLSELECTED(Marks), Marks[Mark])

On the screen below you can see it calculates rank properly over the entire data set.

Initital view

However, when I filter by year (or by school or both, it doesn't matter) I get this:

Wrong ranking

You can see that it does not re-calculate the rank as it should be to rank only displayed values.

I tried to change the formula to something like this:

MarkRank = RANKX(ALLSELECTED(Marks[StudentID]), Marks[Mark])

or

MarkRank = RANKX(ALLSELECTED(Marks[Year]), Marks[Mark])

and it would produce only 1 regardless of the field I choose in ALLSELECTED.

Ultimately, all I need is to have ranking calculated exclusively over selected values using filters from any table, Marks or Students.

Download link for PBI project: https://file.io/BkfZ8UmI

amitchandak · Answer

DMis , Refer to these. They converted most of the stuff related to Rank
For Rank Refer these linkshttps://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measureshttps://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columnshttps://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

dmis · Answer

Thank you Amit.&nbsp;I have seen these articles. I don't believe my problem lies with what's described there, but rather with ALLSELECTED.

anonymous · Answer

DMis&nbsp;If you want to have a dynamic value, try create measures instead of a column.&nbsp;&nbsp;You need two measures separately, Try something like:&nbsp;&nbsp;Mark measure = Sum(Marks[Mark])

Rank Mark = RANKX(ALLSELECTED('Marks'),Mark measure,,Dese,Dense)&nbsp;&nbsp;Paul Zheng _ Community Support TeamIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dmis · Answer

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.&nbsp;Following your advice I created two measures against Marks table:MM = SUM(Marks[Mark])
MarkRankMeasure = RANKX(ALLSELECTED(Marks),[MM],,DESC,Dense)&nbsp;Then I added MarkRankMeasure to the report view and had all records multiplied&nbsp;The upside is the rank is calculated properly now. However, what can I do about row multiplication?

anonymous · Answer

DMis&nbsp;In this case, you would need to rank by another columns (e.g. studentID). So you should have 3 measure in total:&nbsp;&nbsp;&nbsp;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 )
        ))&nbsp;&nbsp;&nbsp;Paul Zheng _ Community Support TeamIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;

Forum Discussion

ALLSELECTED and RANKX not working as expected

8 Replies

Related content

RANKX(ALLSELECT()) not working as expected

dbt job schema not working as expected

Semantic Model not working as expected

Power BI: Model is not working as expected

Expression.SyntaxError: Token ',' expected.