DAX Formula - Return a calculated column MAX after grouping multiple other columns

[pelowski]

I know this should be easy but despite trying and trying different options and searching for a couple hours, I can't get this to work.

As an example, I have scores in a table like this...

Player	Date	Court	Game	Score
Andy	12/20/2017	1	1	15
Bert	12/20/2017	1	1	10
Jeff	12/20/2017	1	1	10
Ryan	12/20/2017	1	1	15
Hank	12/20/2017	2	1	4
Pete	12/20/2017	2	1	18
Mike	12/20/2017	2	1	18
Kevin	12/20/2017	2	1	4

I'd like to return a new calculated column value of the MAX score for a combination of 3 grouped values, the date, the court, and the game. So essentially 15 for the first 4 rows and 18 for the second 4 rows and so on.

The closest attempts I've found are

=CALCULATE(MAX('Scores'[Score]), GROUPBY(Scores,[Date],[Court],[Game]))

but that seems to simply return the Score value for the current row.

=MAXX(SUMMARIZE(Scores,[Date],[Court],[Game],"Max Score of Specific Game",MAX([Score])),[Max Score of Specific Game])

seems to always return the max regardless of how many different games are showing.

I've looked and looked for solutions to this and I just can't figure out what I'm doing wrong. I'm new to DAX and while this would be easy for me as a SQL query, I'm struggling with the DAX representation of it. I'm sure it's likely a simple solution and any help you can provide will be appreciated.

[Zubair_Muhammad · Accepted solution]

Hi @pelowski

Try this Column

MyMax =
CALCULATE (
    MAX ( Scores[Score] ),
    ALLEXCEPT ( Scores, Scores[Date], Scores[Court], Scores[Game] )
)

[JQuon]

I'm also looking to do the same and this solution almost gets me there.  Is it possible to only show the max value in the row that has the value and leave the remaining blank?  For Example in the section in the blue box, Is it possible to show the highest score of 18 only in the row that has the score of 18 and leave the other rows blank?

[Zubair_Muhammad]

@pelowski

[pelowski]

Yes!  That did it!  Thank you!