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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

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

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)
)
Ashish_Mathur
Super User
Super User

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/
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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.

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

Community Support Team _ Rena
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 @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

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...

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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