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

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