Max value with date

[Anonymous]

Hi,

I need to get the max value in a category with its corresponding date (preferably in DAX).

the example as follow

Table1
City	Value	Date
RIY	10	10/9/2019
RIY	25	10/10/2019
RIY	11	10/11/2019
MKK	8	10/10/2019
MKK	9	10/9/2019

The result should be like this

Result
City	Value	Date
RIY	25	10/10/2019
MKK	9	10/9/2019

Thank You in advance for your help

[amitchandak]

Please try

https://community.powerbi.com/t5/Desktop/highest-value-by-category/td-p/428758

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

[RMB · Accepted solution]

Thought it would be worth mentioning that the first answer will still have all the original values with an extra column identifying the highest for a product type without further work. I'm adding this in case a new table is wanted that only has those highest values and not the whole original table.

First, add a calculated column to your table that creates the rank per city.

Rank Column

Rank = 
RANKX (
    FILTER(
    'Table',
    'Table'[City] = EARLIER('Table'[City])
    ),
    'Table'[Value],
    ,
    DESC,
    DENSE
)

Now create a new table that selects only each city entry that was ranked 1.

New table that only has cities that were ranked 1

Table2 = 
SELECTCOLUMNS(
    FILTER( 'Table', 'Table'[Rank] = 1),
    "City", 'Table'[City],
    "Value", 'Table'[Value],
    "Date", 'Table'[Date]
)

When you create your table visual now from the values on the new table you will only have the ranked 1st entries.
Comparsion

[Anonymous]

Thank you so much, it did the trick for me.