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:

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

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,