Summarize Table and Grab value based off expression

[mgirou]

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

[Greg_Deckler]

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

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

[Greg_Deckler · Accepted solution]

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.