Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mgirou
Helper II
Helper II

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

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.