Return date with highest score

[Anonymous]

I am trying to figure out the best way to return a date that the highest score occurred on.  For example:

I have the daya above and I have a Visualization that shows the average score per 'NAME' and theMAX SCORE per 'NAME'.  I am trying to figure out how to display when that MAX SCORE was. 

I am using Card Visualizations with a slicer so when I click on a user one card shows MAX SCORE and I need to have the other card show what date that occurred on.  Can anyone help me with this?  It seems easy but I am hitting a wall.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can create two calculated columns:

max_score = CALCULATE(
                MAX(Sheet7[score]),
                FILTER(ALLSELECTED(Sheet7),Sheet7[Name]=EARLIER(Sheet7[Name])))

max_date = CALCULATE(
                MAX(Sheet7[date]),
                FILTER(ALLSELECTED(Sheet7),Sheet7[Name]=EARLIER(Sheet7[Name])))

Best Regards,

Liang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous  am still working on this. here is the current status.

I got the score correctly. but there is an issue with Date. even though am filtering with score and Name still am not getting corrct result.

MaxScore = MAX(Employee[score])

MaxDate = CALCULATE(MAX(Employee[Date]),

                            FILTER(Employee,Employee[score]=[MaxScore]

                                                        && Employee[Name] = SELECTEDVALUE(Employee[Name])))

[hnguy71]

@Anonymous ,
I think you're on the right track. You can try adding an ALL remove filter context to return all the names, and then filter it down by date. Try this:

ScoreMaxDate = 
var _Score = MAX(Employee[SCORE])
var _SelectedUser = SELECTEDVALUE(Employee[NAME], BLANK())
RETURN
IF(NOT ISBLANK(_SelectedUser), CALCULATE(MAX(Employee[DATE]), ALL(Employee[NAME]), FILTER(Employee, _Score = Employee[SCORE] && _SelectedUser = Employee[NAME])))