topic Re: Return Top Result in Matrix using DAX in DAX Commands and Tips

Return Top Result in Matrix using DAX

AltGr9 — Mon, 16 May 2022 11:00:58 GMT

Hi I have a table of data that looks like this:

And when I put it into a Matrix it looks like this:

And what I'm looking for is a Matrix that looks like this:

I've tried various things in the .pbix attached but none of them work.

I'd like to achieve it using DAX if possible, so that I can add to it in future. Can you help?

Thanks in advance.

https://infinis-my.sharepoint.com/:u:/p/john_altamura/ERe8yEjSdkFLo5h3NbtwcmkBIHXqZiBnw05Qsr__C65-9w?e=gdn4UE

Re: Return Top Result in Matrix using DAX

DimaMD — Mon, 16 May 2022 13:57:13 GMT

Hi AltGr9
Add the Class_new and Row Header columns to the data table

Class_new = SWITCH( TRUE(), [Class] = "Class 1", "Class A", [Class] = "Class 2", "Class B", [Class] = "Class 3", "Class C") --------------------------------------- Row Header = "Top Site"

Follow these steps

Тop Site = VAR _maxdate = CALCULATE ( MAX ( 'Data'[Amount] ), ALLEXCEPT ( 'Data', 'Data'[Class_new] ) ) RETURN CALCULATE ( MAX ( 'Data'[Site] ), FILTER ( 'Data', 'Data'[Class] = MAX ( 'Data'[Class] ) && 'Data'[Amount] = _maxdate ) )

Re: Return Top Result in Matrix using DAX

tamerj1 — Mon, 16 May 2022 14:01:01 GMT

Hi AltGr9

Try

Top Site =
MAXX ( FILTER ( TableName, TableName[Amont]=MAX(TableName[Amont]), TableName[Site])

please make sure to enable "Values on rows"