Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Max value with date

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...
  • RMB's avatar
    RMB
    6 years ago

    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