Select row with MAX value per category Power BI/DAX

Question

How to select the row with a max value per category in DAX/SSAS? Suppose we have table:

+----------+-------+------------+

| Category | Value | Date |

+----------+-------+------------+

| apples | 1 | 2018-07-01 |

| apples | 2 | 2018-07-02 |

| apples | 3 | 2018-07-03 |

| bananas | 7 | 2018-07-04 |

| bananas | 8 | 2018-07-05 |

| bananas | 9 | 2018-07-06 |

+----------+-------+------------+

Desired results are:

+----------+-------+------------+

| Category | Value | Date |

+----------+-------+------------+

| apples | 3 | 2018-07-03 |

| bananas | 9 | 2018-07-06 |

+----------+-------+------------+

Seymour Seymour · Accepted Answer · 2020-04-22T10:05:00

From your question it is not clear whether you want to return a table or a measure.

If you need to return a table, then go with this:

calculated_table
:=VAR _categoryfirst=
    ADDCOLUMNS (
        VALUES ( 'Table'[Category] ),
        "MinValue", CALCULATE (MIN ( 'Table'[Value] ),ALL ( 'Table'[Value] ))
    )
RETURN
CALCULATETABLE(
    SELECTCOLUMNS('Table',
        "Category", 'Table'[Category]
        "Value",  'Table'[Value]
        "Date",  'Table'[Date]
    ),
    KEEPFILTERS (
        TREATAS (
        _categoryfirst,
        'Table'[Category],
        'Table'[Value]
        )
    )
)

Instead, if you want to create a Table visualization with Category, Value, Date showing only the row associated with minimum value I would go with this:

measure:=
VAR _categoryfirst=
    CALCULATETABLE(
        ADDCOLUMNS (
        VALUES ( 'Table'[Category] ),
        "MinValue", CALCULATE (MIN ( 'Table'[Value] ),ALL ( 'Table'[Value] ))
        ),
        ALL('Table'[Value]),
        ALL('Table'[Date])
    )
RETURN
CALCULATE(
    MIN('Table'[Value]),
    KEEPFILTERS (
        TREATAS (
        _categoryfirst,
        'Table1'[Category],
        'Table1'[Value]
        )
    )
)

Select row with MAX value per category Power BI/DAX

1 Answers