Lookup Min/Max 09-02-2020 05:57 AM

[Greg_Deckler]

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

[AlexisOlson]

@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

[Alyona_BI]

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.

[Anonymous]

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