Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
@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.
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.
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.
@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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
88 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |