DAX GROUPBY each group

[shivkonar]

Hi,

I have a table like below in Power BI:

I want a table which return MAX(Rate) for each group, along with the group i.e. a table like below:

How can I achieve this?

Thanks,

Shiv

[v-danhe-msft]

Hi @shivkonar,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[Anonymous]

I suggest you check out the URL below: 

https://stackoverflow.com/questions/46582767/groupby-and-aggregate-in-dax

one of the best to describe the GROUP BY and aggregation functions. 

[Greg_Deckler]

Maybe something like this:

Measure Rate = MAX([Rate])

Measure Day = 
MAXX(FILTER('Table',[Rate]=[Measure Rate]),[Day])

Put these two measures in a table viz with [Name].

[Anonymous]

Why we can't use an expression like below: 

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

[shivkonar]

@Anonymous I get this error

"Function GROUPBY sclar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or direct reference the columns in CurrentGroup()"

[Anonymous]

Yes, Exactly. I receive the same error. And I don't understand what is the probelm 

[Greg_Deckler · Accepted solution]

Table 2 = 
VAR __tmpTable = SUMMARIZE('Table31',[Name],"__MaxRate",MAX('Table31'[Rate]))
RETURN
ADDCOLUMNS(__tmpTable,"__Day",MAXX(FILTER(ALL('Table31'),[Rate]=[__MaxRate]),[Day]))

Table31 is my base table.

[shivkonar]

Thanks @Greg_Deckler

Is it possible to get a calculated table?