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.

     

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
Community 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.

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

      Anonymous ,

       

      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.

      Could you explain more about this? From the data you have given, in year 2018, there's no "Mohan" in EmployeeName.

       

      Regards,

      Jimmy Tao