Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Misunderstanding Lookupvalue

Hello all,  I'm trying to do something which on the surface seems very simple but I'm struggling to make it work.   I have a table of users which are taking periodic tests.  I've created a measur...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    Anonymous

    There are several things you can do first

    Last Score Measure 1 = 
CALCULATE (
    LASTNONBLANK ( 'Table'[Score], 1 ),
    VALUES ( 'Table'[Student] ),
    LASTDATE ( 'Table'[Date] )
)

    Or you can imporve the above by calculating the average score for all students that took a test on the last day to be displayed in the total row

    Last Score Measure 2 =
IF (
    HASONEVALUE ( 'Table'[Student] ),
    CALCULATE (
        LASTNONBLANK ( 'Table'[Score], 1 ),
        VALUES ( 'Table'[Student] ),
        LASTDATE ( 'Table'[Date] )
    ),
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[Score] ),
            VALUES ( 'Table'[Student] ),
            LASTDATE ( 'Table'[Date] )
        ),
        CALCULATE ( DISTINCTCOUNT ( 'Table'[Student] ), LASTDATE ( 'Table'[Date] ) ),
        0
    )
)

    My Measures work with or without a separate student table!

    Hope this helps! :smileyhappy:

truch2's avatar
truch2
Frequent Visitor
9 years ago

How about pulling the three values you want ([TestScore],[TestDate],[Last Test Date]) into a calculated table with the student name on your visual. Make a slicer that selects on the students name, and the table should update to show the test score, date, last test date values for the selected student. 

 

An alternative strategy would be to generate three seperate measures like:

 

Test Date = TestDate([TestDate])

Test Score = TestScore([TestScore])

Last Test Date = LastDate([TestDate])

 

Good luck!

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thanks,

     

    Ideally I'd like a table of user names with their last test score on it rather than have to look at each user individually.

     

    Thanks for your suggestion.