Forum Discussion
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 employees based on their marks also the row numbers.
Actual Data:
| EmployeeName | Year | Total Marks |
| Sofia | 2019 | 97 |
| Mohan | 2019 | 96 |
| Peter | 2019 | 90 |
| Abhishek | 2019 | 90 |
| Chris | 2018 | 90 |
| Mike | 2018 | 58 |
| Chaitanya | 2018 | 58 |
| Paul | 2018 | 47 |
| Raman | 2017 | 45 |
| Bliss | 2017 | 34 |
| Vinod | 2017 | 34 |
| Karthik | 2017 | 29 |
| Anand | 2016 | 23 |
| Raju | 2016 | 23 |
For example 1: If I select 2019 as Year, then my expected output should be
| EmployeeName | Total Marks | Rank | Row Number |
| Sofia | 97 | 1 | 1 |
| Mohan | 96 | 2 | 2 |
| Peter | 90 | 3 | 3 |
| Abhishek | 90 | 3 | 4 |
For example 2: If I select 2017 as Year, then my expected output should be
| EmployeeName | Total Marks | Rank | Row Number |
| Raman | 45 | 1 | 1 |
| Bliss | 34 | 2 | 2 |
| Vinod | 34 | 2 | 3 |
| Karthik | 29 | 3 | 4 |
For example 3: If I select all Years, then my expected output should be
| EmployeeName | Total Marks | Rank | Row Number |
| Sofia | 97 | 1 | 1 |
| Mohan | 96 | 2 | 2 |
| Peter | 90 | 3 | 3 |
| Abhishek | 90 | 3 | 4 |
| Chris | 90 | 3 | 5 |
| Mike | 58 | 4 | 6 |
| Chaitanya | 58 | 4 | 7 |
| Paul | 47 | 5 | 8 |
| Raman | 45 | 6 | 9 |
| Bliss | 34 | 7 | 10 |
| Vinod | 34 | 7 | 11 |
| Karthik | 29 | 8 | 12 |
| Anand | 23 | 9 | 13 |
| Raju | 23 | 9 | 14 |
I got the Rank covered as expected using the below Rank measure.
Rank =
RANKX (
ALLSELECTED ( Employee[EmployeeName] ),
CALCULATE ( SUM ( Employee[totalMarks] ) ),
,
DESC,
DENSE
)Can someone help me with how to get this Row_Number in Power BI using DAX to work dynamically on Slicer selection?
Thanks in advance!
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.
5 Replies
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 ,
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.
