cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shivkonar Regular Visitor
Regular Visitor

DAX GROUPBY each group

Hi,

 

I have a table like below in Power BI:

Screen Shot 2018-09-06 at 14.05.44.png

 

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

Screen Shot 2018-09-06 at 14.07.05.png

 

How can I achieve this?

 

Thanks,

Shiv

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX GROUPBY each group

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


8 REPLIES 8
Super User
Super User

Re: DAX GROUPBY each group

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].


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


shivkonar Regular Visitor
Regular Visitor

Re: DAX GROUPBY each group

Thanks @Greg_Deckler

 

Is it possible to get a calculated table?

Re: DAX GROUPBY each group

Why we can't use an expression like below: 

 

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

 

shivkonar Regular Visitor
Regular Visitor

Re: DAX GROUPBY each group

@elahemeydani 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()"

Re: DAX GROUPBY each group

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

Super User
Super User

Re: DAX GROUPBY each group

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Re: DAX GROUPBY each group

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. 

Community Support Team
Community Support Team

Re: DAX GROUPBY each group

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.