Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Lookup text from another table based on highest value

Hi All, I am trying to create a column that shows the highest priority contact from another table. If there's a tie for highest priority, I would like to get the first one. Here's what my data loo...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    We can insert an index column in power query in table 1. Then we can create a calculated column as below.

    Column = 
VAR pr =
    CALCULATE (
        MAX ( 'Table 1'[Priority] ),
        FILTER ( 'Table 1', 'Table 1'[company ID] = 'Table 2'[ID] )
    )
VAR minindex =
    CALCULATE (
        MIN ( 'Table 1'[Index] ),
        FILTER (
            'Table 1',
            'Table 1'[Priority] = pr
                && 'Table 1'[company ID] = 'Table 2'[ID]
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table 1'[Contact Name] ),
        FILTER ( 'Table 1', 'Table 1'[Priority] = pr && 'Table 1'[Index] = minindex )
    )

     

    Pbix as attached.

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous ,

 

We can insert an index column in power query in table 1. Then we can create a calculated column as below.

Column = 
VAR pr =
    CALCULATE (
        MAX ( 'Table 1'[Priority] ),
        FILTER ( 'Table 1', 'Table 1'[company ID] = 'Table 2'[ID] )
    )
VAR minindex =
    CALCULATE (
        MIN ( 'Table 1'[Index] ),
        FILTER (
            'Table 1',
            'Table 1'[Priority] = pr
                && 'Table 1'[company ID] = 'Table 2'[ID]
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table 1'[Contact Name] ),
        FILTER ( 'Table 1', 'Table 1'[Priority] = pr && 'Table 1'[Index] = minindex )
    )

 

Pbix as attached.