cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
olimilo Member
Member

Getting the most recent and second most recent values by group (without dates)

Hi everyone,

 

I'm trying to get the most recent and second most recent values in my table. I've looked up several examples on here, but the my data doesn't have a date value to check the most recent value with, although I can use a TestID to check the chronological order of the data with (eg: lower TestID means this was done earlier).

 

Any suggestion on how I can go about with this?

 

StudentIDTestCodeTestIDRating
Student0061Test0000229947092.54
Student0061Test0000231861038.15
Student0061Test0000331861054
Student0061Test0000631861043.91
Student0062Test0000331091289
Student0062Test0000332451289
Student0063Test0001130187646.63
Student0063Test00011311438100
Student0064Test0000329687396
Student0065Test0000531137196.82
Student0066Test0000330731175
Student0066Test0000330834477
Student0066Test0000332106874

 

StudentIDTestCodeMost Recent2nd Most Recent
Student0061Test0000238.1592.54
Student0061Test0000354 
Student0061Test0000643.91 
Student0062Test000038989
Student0063Test0001110046.63
Student0064Test0000396 
Student0065Test0000596.82 
Student0066Test000037477
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Getting the most recent and second most recent values by group (without dates)

@olimilo solution attached, look at table2 and page 3 in attached.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
Super User
Super User

Re: Getting the most recent and second most recent values by group (without dates)

@olimilo here are two measures which would work

 

Most Recent = 
CALCULATE( 
    MAX( Table2[Rating] ), 
    FILTER( 
        ALLEXCEPT( Table2, Table2[StudentID], Table2[TestCode] ), 
        Table2[Index] = MAX( Table2[Index] ) 
    ) 
) 

Most Recent 2 = 
CALCULATE( 
    MAX( Table2[Rating] ), 
    FILTER( 
        ALLEXCEPT( Table2, Table2[StudentID], Table2[TestCode] ), 
        Table2[Index]=  MAX( Table2[Index] ) - 1 
    )
) 





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





olimilo Member
Member

Re: Getting the most recent and second most recent values by group (without dates)

Hi @parry2k 

 

Thank you, but unfortunately both measures didn't work for me. Also, using an Index - 1 filter for the 2nd most recent took data from other students and other test codes.

 

Super User
Super User

Re: Getting the most recent and second most recent values by group (without dates)

@olimilo I think I missed to point out that you need to add index field using query editor. 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Super User
Super User

Re: Getting the most recent and second most recent values by group (without dates)

@olimilo solution attached, look at table2 and page 3 in attached.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 156 members 1,638 guests
Please welcome our newest community members: