Conditional LOOKUP in PowerBI

[Anonymous]

Hello, I have a table with four columns - Store, Code, Avg Dist and Max Avg Dist(Max of Avg Dist for each store)
I am trying to find the Code which has the Max Avg Dist for each store. For example in below table - For Store A, the max avg dist is 2000 which is for code '123' and this code is populated in the right most column.

Store	Code	Avg Dist	Max Avg Dist	Code with High Avg Dist
A	123	2000	2000	123
A	234	1020	2000	123
A	456	1600	2000	123
B	123	1560	1900	789
B	456	1340	1900	789
B	789	1900	1900	789
B	91	1260	1900	789

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

You can create a column as below:-

Code with High Avg Dist = 
VAR _high_avg =
    CALCULATE (
        MAX ( 'Table (2)'[Avg Dist] ),
        FILTER ( 'Table (2)', 'Table (2)'[Store] = EARLIER ( 'Table (2)'[Store] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table (2)'[Code] ),
        FILTER (
            'Table (2)',
            'Table (2)'[Avg Dist] = _high_avg
                && 'Table (2)'[Store] = EARLIER ( 'Table (2)'[Store] )
        )
    )