Lookup text from another table based on highest value

[Anonymous]

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 looks like, and what I'm hoping for:

Please help!!!

[v-frfei-msft · Accepted solution]

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.

[parry2k]

@Anonymous add following measure

Contact = 
CALCULATE( 
MIN( Contact[Contact Name] ), 
TOPN(1, Contact, Contact[Priority], DESC ) 
)

[Cmcmahan]

When you say the "first one" on ties, do you mean in alphabetical order? Or do you mean Hermione because she literally comes first in the way the table is currently ordered?

You can use TOPN to get the result you're looking for. I created this as a measure instead of a calculated column. You can read more on why here.  If you desperately need this as a calculated column instead, you'll have to FILTER the 'Table 1' parameter in the TOPN function to only include those in the current company.

PriorityPerson = 
CALCULATE(
SELECTEDVALUE( 'Table 1'[Contact Name]),
    TOPN(
        1, 'Table 1', 
        [Priority], DESC, 
        [Contact Name], ASC)
)

If you want the secondary priority ordering to be based on the table as it currently stands, you'll need to add an index to the table original table when it's in the order you want, and use that instead of [Contact Name] in the TOPN function.