Forum Discussion
Lookup value that another value on same row is max
Dear friends
how can i lookup type from table1 that have max value for each name in table 2
for example
Table 1
| Name | Type | Value |
| Jame | A | 10 |
| Jame | B | 20 |
| John | C | 5 |
| John | D | 15 |
Table2
| Name | Type |
| Jame | B |
| John | D |
i need to show type that have max value for each name
thank you in advance for any suggestion.
Hi, username
Try to create a calculate column below:
_COL = VAR _MAXValue = MAXX ( FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[name] ) ), 'Table'[Value] ) RETURN IF ( 'Table'[Value] = _MAXValue, 'Table'[Type], BLANK () )If you want to summarize in another table you can create a calculate table using the following formula:
Table2 = VAR _maxValue = SELECTCOLUMNS ( 'Table', "1", CALCULATE ( MAX ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Name] ) ) ) RETURN SUMMARIZE ( FILTER ( 'Table', [Value] IN _maxValue ), [Name], [Type] )I created a simple sample to illustrate this.
Sample:
Result:
Table2:
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
Anonymous , is table 2 is what you need ?
or what is the expected output
amitchandak Type column from table 1 for each name that have max value on value column
- amitchandak
Anonymous , Not sure I got it correctly. Like
maxx(filter(Table1, [Name] =earlier([name])), [Value])
- v-angzheng-msft
Community Support
Hi, username
Try to create a calculate column below:
_COL = VAR _MAXValue = MAXX ( FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[name] ) ), 'Table'[Value] ) RETURN IF ( 'Table'[Value] = _MAXValue, 'Table'[Type], BLANK () )If you want to summarize in another table you can create a calculate table using the following formula:
Table2 = VAR _maxValue = SELECTCOLUMNS ( 'Table', "1", CALCULATE ( MAX ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Name] ) ) ) RETURN SUMMARIZE ( FILTER ( 'Table', [Value] IN _maxValue ), [Name], [Type] )I created a simple sample to illustrate this.
Sample:
Result:
Table2:
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
