Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |