Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic Row_Number

Hi, I have a requirement where I have employees and their marks by class and year as mentioned below. In the below example, Year is my slicer. Based on this slicers selection I have to rank the empl...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous ,

     

    You need to create an addtional index and then create a measure using DAX below:

    Row Number = 
COUNTROWS (
    FILTER (
        ALLSELECTED ( Employee ),
        COUNTROWS (
            FILTER (
                Employee,
                Employee[Total Marks] <= EARLIER ( Employee[Total Marks] )
                    && Employee[Index] >= EARLIER ( Employee[Index] )
            )
        )
    )
)

     

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

jthomson's avatar
jthomson
Icon for Solution Sage rankSolution Sage
7 years ago

It's exactly the same as your RANKX function, just find some way to break ties and incorporate that in it - maybe just add an index and use that

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi jthomson,

     

    Thanks for your response. I tried with Index, but no luck.

    • v-yuta-msft's avatar
      v-yuta-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Anonymous ,

       

      You need to create an addtional index and then create a measure using DAX below:

      Row Number = 
COUNTROWS (
    FILTER (
        ALLSELECTED ( Employee ),
        COUNTROWS (
            FILTER (
                Employee,
                Employee[Total Marks] <= EARLIER ( Employee[Total Marks] )
                    && Employee[Index] >= EARLIER ( Employee[Index] )
            )
        )
    )
)

       

       

      Community Support Team _ Jimmy Tao

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi v-yuta-msft 

         

        Thanks for your solution. This is great! But the thing is it is working for the pre-calculated ranks. But as per your screenshots, you selected 2019 and we have 4 students listed with rank and row numbers which is correct. In my scenario, out of those 4 students, Mohan is in row 2 and rank 2. Mohan may also present in rank 1 and row 1 when we select 2018.

         

        In this dynamic scenario, it is not working because the row number is calculating based on the predefined Index values.