Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

group by table

Hello

 

I have a table A and I want to make a new table from A to B.

I want to get in the B table the maximum value from the column results for each id. Also for this particular maximum I want to get the value from the threshold and number columns.

 

I tried to make a "group by" in modeling --> new table. I got the max (results)per id column but I don't know how to get the relevent info from the others column "threshold" and  "number" to Table B.

I attached here the exemple. 

Thanks  a lot

 
 
 
 
 
 
 
 
 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - Try:

     

     

    Table (22)B = 
  ADDCOLUMNS(
    SUMMARIZE('Table (22)',[id],"max",MAX('Table (22)'[results])),
    "threshold",MAXX(FILTER('Table (22)',[id]=EARLIER([id])&&[results]=[max]),[threshold]),
    "number",MAXX(FILTER('Table (22)',[id]=EARLIER([id])&&[results]=[max]),[number])
  )

     

    I verified this in the attached PBIX below sig. Table (22) and Table (22)B.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - Try:

     

     

    Table (22)B = 
  ADDCOLUMNS(
    SUMMARIZE('Table (22)',[id],"max",MAX('Table (22)'[results])),
    "threshold",MAXX(FILTER('Table (22)',[id]=EARLIER([id])&&[results]=[max]),[threshold]),
    "number",MAXX(FILTER('Table (22)',[id]=EARLIER([id])&&[results]=[max]),[number])
  )

     

    I verified this in the attached PBIX below sig. Table (22) and Table (22)B.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      thank you very much! it rked!

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    Table B =summarize(tableA, tableA[ID], "Max Result" , max(TableA[result]),"thershold", firstnonblank(Table[thershold],blank()),"number", firstnonblank(Table[number],blank()))