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:

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

        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,