topic Re: Lookup Min/Max in Quick Measures Gallery

Lookup Min/Max

Greg_Deckler — Mon, 27 Apr 2020 18:01:07 GMT

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

eyJrIjoiNjA5NzBlMWMtZDY0ZS00N2I1LWFmM2EtNmJlOTJmMjZiYzJhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Lookup Min/Max

Anonymous — Mon, 27 Apr 2020 17:49:20 GMT

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

Re: Lookup Min/Max

Alyona_BI — Wed, 02 Sep 2020 12:57:16 GMT

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!

Re: Lookup Min/Max

Greg_Deckler — Wed, 02 Sep 2020 13:11:12 GMT

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.

Re: Lookup Min/Max

AlexisOlson — Thu, 23 Mar 2023 14:50:55 GMT

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