Forum Discussion

shivkonar's avatar
shivkonar
Frequent Visitor
8 years ago
Solved

DAX GROUPBY each group

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

Need Help
Show and Tell
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago
    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.

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      Why we can't use an expression like below: 

       

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

       

      • shivkonar's avatar
        shivkonar
        Frequent Visitor
        8 years ago

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

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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