cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
akhilduvvuru10 Regular Visitor
Regular Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic Row_Number

@akhilduvvuru10 ,

 

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.

 

5 REPLIES 5
jthomson New Contributor
New Contributor

Re: Dynamic Row_Number

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

akhilduvvuru10 Regular Visitor
Regular Visitor

Re: Dynamic Row_Number

Hi @jthomson,

 

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

Community Support Team
Community Support Team

Re: Dynamic Row_Number

@akhilduvvuru10 ,

 

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.

 

akhilduvvuru10 Regular Visitor
Regular Visitor

Re: Dynamic Row_Number

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.

Community Support Team
Community Support Team

Re: Dynamic Row_Number

@akhilduvvuru10 ,

 


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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 353 members 3,680 guests
Please welcome our newest community members: