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        Type

m12       23            Data

m12       14            Video

m3         12            RHP

m43       55            Data

m43       60            Video

 

I've been trying to use summarize and get the MAX of value, but i don't know how to just add the 'Type' of the max value. So the output table should be (The goal is to have Column ID to have no duplicates):

 

ID         Value       Type

m12      23           Data

m3        12           RHP

m43      60           Video

 

Any help would be appreciated.

 

Thanks

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. 

3 Replies

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