Forum Discussion

mgirou's avatar
mgirou
Helper II
8 years ago
Solved

Summarize Table and Grab value based off expression

I have a Calculated Table that  i am trying to summarize to return the Unique ID with the greatest value and the type that the greatest value is. Here is example table of data   ID         Value   ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Well, you didn't say that and your example didn't supply that information. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

     

    That being said, just add a column to your source table like this:

     

    Column = CONCATENATE('#Table'[ID],'#Table'[Value])

     

    and then change the code I supplied to this:

     

    #TableA = 
VAR tableA = SUMMARIZE('#Table','#Table'[ID],"Value",MAX('#Table'[Value]))
VAR tableB = ADDCOLUMNS(tableA,"UniqueID",CONCATENATE([ID],[Value]))
VAR tableC = ADDCOLUMNS(tableB,"Type",LOOKUPVALUE('#Table'[Type],'#Table'[Column],[UniqueID]))
RETURN tableC

    And if you are going to tell me that ID and Value can be duplicated, then just concatenate on your Type to build your unique ID. If THAT is a duplicate, then add an Index column on your import. If all of that still creates duplicates, too bad you are out of luck. 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago
#TableA = 
VAR tableA = SUMMARIZE('#Table','#Table'[ID],"Value",MAX('#Table'[Value]))
VAR tableB = ADDCOLUMNS(tableA,"Type",LOOKUPVALUE('#Table'[Type],'#Table'[Value],[Value]))
RETURN tableB

#Table is my original table based on your data.

  • mgirou's avatar
    mgirou
    Helper II
    8 years ago

    This will not work because Value column and Unique has duplicates so lookupvalue will return error "Table of mulitple values was supplied..."

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      Well, you didn't say that and your example didn't supply that information. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

       

      That being said, just add a column to your source table like this:

       

      Column = CONCATENATE('#Table'[ID],'#Table'[Value])

       

      and then change the code I supplied to this:

       

      #TableA = 
VAR tableA = SUMMARIZE('#Table','#Table'[ID],"Value",MAX('#Table'[Value]))
VAR tableB = ADDCOLUMNS(tableA,"UniqueID",CONCATENATE([ID],[Value]))
VAR tableC = ADDCOLUMNS(tableB,"Type",LOOKUPVALUE('#Table'[Type],'#Table'[Column],[UniqueID]))
RETURN tableC

      And if you are going to tell me that ID and Value can be duplicated, then just concatenate on your Type to build your unique ID. If THAT is a duplicate, then add an Index column on your import. If all of that still creates duplicates, too bad you are out of luck.