Using Group By and Max function together DAX

Question

I have a table like below:

and I want to group by the date and name and then order by the MAX of rate. I use such an Expression:

NewTable =
CALCULATETABLE (
    Table1,
    GROUPBY ( Table1, Table1[Day], Table1[Name], "maxrate", MAX ( Table1[Rate] ) ))

But I receive an error. Can anyone explain how max and group by can be used together in DAX?

RADO RADO · Accepted Answer · 2018-09-07T09:53:21

Just use SUMMARIZE function instead of GROUPBY:

New Table  = SUMMARIZE (Table1, Table1[Day], Table1[Name], "maxrate', MAX(Table1[Rate]))

GROUPBY requires an iterator (such as MAXX). For example, let's say your table has rate and quantity, and your want to calculate max amount (rate * quantity). Then you should use GROUPBY:

New Table =
GROUPBY (
    Table1,
    Table1[Day],
    Table1[Name],
    "Max Amount", MAXX ( CURRENTGROUP (), Table1[Rate] * Table1[Quantity] )
)

Here, you first group table1 by day and name, and then iterate current group, to find max amount.

GROUPBY is very handy in some complicated cases, but your situation seems straightforward.

Using Group By and Max function together DAX

1 Answers