Community ChampionLookup Min/Max
This one or some variation of it comes up constantly in the forums so I'm surprised that there is no pattern in the Quick Measure Gallery for dealing with it. The problem generally goes along the lines of this:
"I have this table of data with these items in it and values for various blah. I need to create a measure that returns which item has the lowest/highest sum/average/blah."
That's not a direct quote, I'm paraphrasing...
Anyway, the pattern looks like this:
Lookup Min =
VAR __Table =
SUMMARIZE(
'Table',
[Item],
"__Value",SUM('Table'[Value])
)
VAR __Min = MINX(__Table,[__Value])
RETURN
MINX(FILTER(__Table,[__Value] = __Min),[Item])
Lookup Max =
VAR __Table =
SUMMARIZE(
'Table',
[Item],
"__Value",SUM('Table'[Value])
)
VAR __Max = MAXX(__Table,[__Value])
RETURN
MAXX(FILTER(__Table,[__Value] = __Max),[Item])
4 Replies
thanks Greg,
my table looks like this, so there are multiple repeating scenario ids, and for example COP-0001 will have step numbers 1-35, i basically need to return the lowest, which should be 1 and the highest, 35, and access those date records.
Im trying your code, but not sure about the __Table and ___Value parts/
Could you paste an example code following your pattern
Business Unit Stream Scenario ID Step Number Planned Date Execution Date
Chile Finance COP-0001 1 2020/04/24 Chile Finance COP-0002 1 2020/04/24 Chile Finance COP-0003 1 2020/04/24 Chile Finance COP-0004 1 2020/04/24 Chile Finance COP-0005 1 2020/04/24 Chile Finance COP-0006 1 2020/04/28 Chile Finance COP-0007 1 2020/04/28 Chile Finance COP-0008 1 2020/04/28 Chile Finance COP-0009 1 2020/04/24 Chile Finance COP-0010 1 2020/04/24 Chile Finance COP-0011 1 2020/04/24 Chile Finance COP-0012 1 2020/04/28 Chile Finance COP-0013 1 2020/04/28 
Alyona_BIHelper II
Hi Greg_Deckler!
Many thanks for your solution. But is there a way to calculate the same with help of calculated column (not as measure as in your example)?
Thanks in advance!
- Greg_Deckler
Alyona_BI - Absolutely, the same technique will work with a calculated column. You may have to use EARLIER to filter down to the correct rows. Hard to be specific.
- AlexisOlson
Super User
Greg_Deckler Iterator X-functions are great but you can also do this without them.
Lookup Max = VAR _TopRow = TOPN ( 1, VALUES ( 'Table'[Item] ), CALCULATE ( SUM ( 'Table'[Value] ) ) ) VAR _TopItem = SELECTCOLUMNS ( _TopRow, "Item", 'Table'[Item] ) RETURN _TopItem
