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

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:

10 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Vvelarde
        Community Champion
        9 years ago

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

         

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