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



Please help!!!

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.

     

3 Replies

  • 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.

     

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    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.

     

     

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous add following measure

     

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