Misunderstanding Lookupvalue

[Anonymous]

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 measure which shows me the date that a user last took a test:

Last Test Date = LastDate([TestDate])

Easy,

Now I'd like to see the last  score achieved by the student so I created another measure

Last Score = LookupValue([TestScore],[TestDate],[Last Test Date])

It always complains that I'm passing multiple values!

I've read that I shouldn't be using Lookups in measure but I can't think of another simple way to achieve what I want? Help greatly appreciated.

Thanks!

[truch2]

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]

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.

[Vvelarde]

@Anonymous

hi, please try something like this:

A measure:

LastScore=If(HasOneValue(Table[User]),Calculate(Values(Table[TestScore]),Filter(Table, Table[TestDate]=[Last Test Date])))

[Anonymous]

@Vvelarde

Thanks for that, it only works if the user has a single test date though. I want to be able to find the last result from a list.

[Vvelarde]

@Anonymous

Hi, asuming you have a similar relationship to this:

Test Table

Create a measure:

LastScore =
VAR LastTestDate =
    LASTDATE ( Test[TestDate] )
RETURN
    IF (
        HASONEVALUE ( Users[User] ),
        CALCULATE (
            VALUES ( Test[TestScore] ),
            FILTER ( Test, Test[TestDate] = LastTestDate )
        )
    )

[Anonymous]

Thanks Very much for your help,

That's the structure of my data but it's still not working. I'm going to create some dummy data to see if I can get to the bottom of the problem.

Wiredchop

[Anonymous]

@Vvelarde

Ok, I've created the simplest example I could but it's not working!?!?!

Really puzzling me why this isn't working. It seems like it should be so simple!

Thanks again for your help,

[Sean · Accepted solution]

@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!

[Anonymous]

Thank you all very much for this

@Vvelarde I realised my test data set wasn't categorising by the separate student table in the visual. Changing this fixed the problem. However, it didn't sort my full data set! 

@Sean THanks very much for this, I'll give it a go and see how I get on. If I'm not successful I'll go back to looking through my dataset for potential conflicts.

I'll report back!

[Anonymous]

Thanks again for all your help, I noticed in my data that some users had repeated dates hence the error. @Sean your solution is compact and also accounts for repeated dates. I like the averaged solution too. I'm still learning DAX and seeing values used in the filter of CALCULATE makes a lot of sense to me now.

Thanks again,