Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
#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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |