Create table of most recent results (with duplicates)

[benh66]

I have a problem that is equivalent to the following. I run a bowling alley, and I want to create a dashboard showing recent scores. I have a table with their names, dates, and scores and I'd like to make a summary showing only the most recent score for each bowler.

A possible example of the data is:

Name	Date	Score
Steve	7/12/2021	300
Richard	6/12/2021	100
Steve	6/12/2021	250
Steve	5/12/2021	270
Bob	5/12/2021	120
Steve	4/12/2021	270
Bob	4/12/2021	130
Richard	4/12/2021	110
Steve	3/12/2021	290
Steve	2/12/2021	280
Bob	2/12/2021	140
Richard	1/12/2021	120

My desired output would be:

Name	Date of last bowl	Most recent score
Bob	5/12/2021	120
Richard	6/12/2021	100
Steve	7/12/2021	300

Thanks to the help from this community I was able to creating two measures to solve this problem (in most cases):

latestdate = CALCULATE(max('Table'[Date]),ALLEXCEPT('Table','Table'[Name]))

latestscore =

var latestdate = CALCULATE(max('Table'[Date]),ALLEXCEPT('Table','Table'[Name]))

Return

CALCULATE(values('Table'[Score]),filter('Table','Table'[Date]=latestdate))

However, this does not work in the case where someone went bowling twice on the most recent date.

The error reads: "MdxScript(Model) (8, 11) Calculation error in measure 'Table'[latestscore]: A table of multiple values was supplied where a single value was expected."

Is there any way to force 'latestscore' to only output one value? I've tried a few things but none have worked so far.

I've attached the pbix file with and without the error (i.e. with and without duplicate dates)

Many thanks for your help! 🙂

This is a follow on from my earlier question here: Create table of most recent results 

[benh66]

PBIX file with error - https://drive.google.com/file/d/1jfMZQiBsBh_fvdPMKSIpqCX5ytlj2jRF/view?usp=sharing

PBIX file with no error - https://drive.google.com/file/d/1XMgmvs0QJ5zw06rFHmKz0M_x-YOdIJQs/view?usp=sharing 

[parry2k]

@benh66  

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@benh66 here is your measure:

Measure 2 = 
VAR __table = CALCULATETABLE ( ALLSELECTED ( Bowling ), VALUES ( Bowling[Name] ) ) 
Var __date =  CALCULATE( MAX(Bowling[ Date] ), __table )
RETURN
CALCULATE ( SUM ( Bowling[Score] ), __table, Bowling[ Date] = __date )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[benh66]

Thanks! Worked perfectly 🙂