Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

EmployeeNameYearTotal Marks
Sofia201997
Mohan201996
Peter201990
Abhishek201990
Chris201890
Mike201858
Chaitanya201858
Paul201847
Raman201745
Bliss201734
Vinod201734
Karthik201729
Anand201623
Raju201623

 

For example 1: If I select 2019 as Year, then my expected output should be

EmployeeNameTotal MarksRankRow Number
Sofia9711
Mohan9622
Peter9033
Abhishek9034

 

For example 2: If I select 2017 as Year, then my expected output should be

EmployeeNameTotal MarksRankRow Number
Raman4511
Bliss3422
Vinod3423
Karthik2934

 

For example 3: If I select all Years, then my expected output should be

EmployeeNameTotal MarksRankRow Number
Sofia9711
Mohan9622
Peter9033
Abhishek9034
Chris9035
Mike5846
Chaitanya5847
Paul4758
Raman4569
Bliss34710
Vinod34711
Karthik29812
Anand23913
Raju23914

 

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!

1 ACCEPTED 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] )
            )
        )
    )
)

Capture.PNG 

 

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.

 

View solution in original post

5 REPLIES 5
jthomson
Solution Sage
Solution Sage

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
Not applicable

Hi @jthomson,

 

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

@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] )
            )
        )
    )
)

Capture.PNG 

 

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.