cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Get a value from the last row for the student

I am new to Power BI and I am trying to get the last test value for a student where the test could have been taken multiple times.  I need to omit the student if they only took the test once.  There are row numbers per student in the table.  The student number field is UMPI_NBR.  the value I want is PHQ9_TOTAL and the row number field is TESTROW.  All I am pulling right now is the largest value of all of the tests beyond the 1st one instead of the value of PHQ9_TOTAL of the MAX TESTROW.

 

Last Visit = SUMX (
    VALUES( Table1[UMPI_NBR] ),
    CALCULATE (MAX(Table1[PHQ9_TOTAL] ), (Table1[TESTROW] > 1) )
)
1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Get a value from the last row for the student

This is working for me.  I don't understand the syntax well enough to know why I don't need the UMPI_NBR specified in there somewhere.

 

Last Visit = CALCULATE (
SELECTEDVALUE ( Table1[PHQ9_TOTAL] ),
FILTER ( ALL ( Table1[VISIT] ), Table1[VISIT] = MAX ( Table1[VISIT] ) && Table1[VISIT] > 1)
)

View solution in original post

10 REPLIES 10
Super User IV
Super User IV

Re: Get a value from the last row for the student

Try like

//1
Last Visit = SUMX (
    VALUES( Table1[UMPI_NBR] ),
    CALCULATE (MAX(Table1[PHQ9_TOTAL] ), filter(Table1,Table1[TESTROW] = max(Table1[TESTROW])) ))
	
//2
rank = rank(Table1[UMPI_NBR],calculate(max(Table1[TESTROW])),,desc,dense)

Last Visit = SUMX (filter(Table1,[rank]=1),Table1[PHQ9_TOTAL] )

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Microsoft yingyinr
Microsoft

Re: Get a value from the last row for the student

Hi @Anonymous ,

I made a minor adjustment on the formula you provided , and now you should get the correct value, please check.

Get a value from the last row for the student.JPG

Last Visit =
VAR a =
    MAX ( 'Table1'[UMPI_NBR] )
VAR b =
    SUMX (
        VALUES ( Table1[UMPI_NBR] ),
        CALCULATE (
            MAX ( Table1[PHQ9_TOTAL] ),
            DISTINCTCOUNT ( Table1[TESTROW] ) > 1
                && 'Table1'[UMPI_NBR] = a
        )
    )
RETURN
    b

 

Best Regards

Rena

Anonymous
Not applicable

Re: Get a value from the last row for the student

This answer gives me the next test score after the 1st one but not necessarily the last one.

Anonymous
Not applicable

Re: Get a value from the last row for the student

The 1st answer is close but I need to exclude the 1st score if the person hasn't taken a 2nd test.

 

The 2nd answer gives me syntax errors at '(' and most of the fields are highlighted as errors.

Super User IV
Super User IV

Re: Get a value from the last row for the student

Please explain what worked, what did not with you formula and example.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Super User IV
Super User IV

Re: Get a value from the last row for the student

Hi,

What do you mean by "I need to omit the student if they only took the test once"?  Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Microsoft yingyinr
Microsoft

Re: Get a value from the last row for the student

Hi @Anonymous ,

In fact , the formula I provided works well in my side... Here is the sample PBIX file I used , please check if there is any diffrence between yours and mine...

If I understand correctly , what you want to get is the total score of the best results of those students that took the test of multiple times. So I got the result by the following steps :

1 . Exclude students that take the test only once

2 . Get the best results from / of students that took the test of multiple times

3 . Summarize the scores obtained above .

For example , the following example contains the test scores of 5 students (S01 ~ S05 ) . Student S01 and S04 took the test only once , so the score of this two students are excluded . In addition , the best scores of S2 , S3 , and S5 are 89 , 82 , and 65 separately . Then the final summarized score is 89 + 82 + 65 = 325 .

get max score of student.JPG

Best Regards

Rena

Anonymous
Not applicable

Re: Get a value from the last row for the student

Hi @yingyinr , @Ashish_Mathur , @amitchandak 

1.  Yes, I want to exclude the score of the students that took the test only once because one of the things I will be using the score for is to check the effectiveness of a training program.  So I need to know if the score improved or not.  So the ones that have only taken the test once, need to be excluded.

2. I want only the LAST score, not the best one and not a summary.  So if student 'A' took the test 6 times:

TESTROW/PHQ9_TOTAL

1                               80

2                               85

3                              70

4                              80

5                              90

6                              68

I need the #6 - 68

Super User IV
Super User IV

Re: Get a value from the last row for the student

Can you share sample data and sample output.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors