Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Return date with highest score

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

2019-12-18_16h11_34.png

 

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.

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

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

 

 

test_max_score.PNG

 

Best Regards,

Liang

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

View solution in original post

3 REPLIES 3
V-lianl-msft
Community Support
Community Support

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

 

 

test_max_score.PNG

 

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
Not applicable

 

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

MaxDateIssue.PNG

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

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.