Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional LOOKUP in PowerBI

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.

 

 

StoreCodeAvg DistMax Avg DistCode with High Avg Dist
A12320002000123
A23410202000123
A45616002000123
B12315601900789
B45613401900789
B78919001900789
B9112601900789
Need Help
Show and Tell
  • Samarth_18's avatar
    Samarth_18
    3 years ago

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

1 Reply

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

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