Forum Discussion

MuppetyMe's avatar
MuppetyMe
Helper I
1 year ago
Solved

LOOKUPVALUE troubles

I have two tables: Table1 has multiple lines referencing the same ID with teammate names on different dates.
On Table2, I want to bring over the teammate name from the most recent date by the ID. Creating a new column on Table2 with LOOKUPVALUE throws an error because there are multiple entries for the same ID.
Created a measure: 

MOST RECENT Teammate = CALCULATE(FIRSTNONBLANK(Table1[Teammate],1), FILTER(Table1, Table1[Date] = MAX(Table1[Date]))), which does produce results, but not sure how to bring that over to Table2. 
 

 

 

Need Help
  • parry2k's avatar
    parry2k
    1 year ago

    MuppetyMe you can use the following expression to add a new column in the table2, assuming table 1 and table 2 has relationship on ID - one to many

     

    Most Recent Teammate = 
CALCULATE ( 
    MAX ( Table1[Teammate] ),
    TOPN ( 
          1,
          RELATEDTABLE ( Table1 ),
          Table1[Date] 
    )
)

     

     

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    MuppetyMe you can use the following expression to add a new column in the table2, assuming table 1 and table 2 has relationship on ID - one to many

     

    Most Recent Teammate = 
CALCULATE ( 
    MAX ( Table1[Teammate] ),
    TOPN ( 
          1,
          RELATEDTABLE ( Table1 ),
          Table1[Date] 
    )
)

     

     

    • MuppetyMe's avatar
      MuppetyMe
      Helper I
      1 year ago

      Thank you parry2k. Once I introduced a table without repeating IDs and created a one to many cardinality, this solution worked. 

  • SachinNandanwar's avatar
    SachinNandanwar
    Impactful Individual
    1 year ago

    Create a calculated column in first table that ranks the record across ID's

     

    Rnk = RANKX (
    FILTER (
        'Tbl_',
 
       'Tbl_'[Id]= EARLIER (Tbl_[ID])
    ),
    'Tbl_'[Date],
    , 
    DESC,Dense
)

     


    In the second table create a measure that retrieves the top most names across each ID's based on the rank

     

    Recent_TeamMate =
CALCULATE (
    MAX ( Tbl_[Name] ),
    FILTER ( RELATEDTABLE ( Tbl_ ), Tbl_[Rnk] = 1 )
)

     

     

     

  • MuppetyMe's avatar
    MuppetyMe
    Helper I
    1 year ago

    Hi parry2k, unfortunately, it's a Many to Many relationship between the 2 tables 😞 Might it be best to create a whole different table with unique IDs, then bring in the MAX, TOPN teammate? 

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    MuppetyMe yes the best practice should be a dimension table with unique values and then it should work.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi MuppetyMe 

     

    Thanks for the reply from parry2k and SachinNandanwar , please allow me to provide another insight:

     

    The following measure is for your reference.

    Measure = 
VAR _MAXDate = CALCULATE(MAX('Table1'[Date]), 'Table1'[ID] = MAX('Table2'[ID]))
RETURN
CALCULATE(MAX('Table1'[Teammate]), FILTER('Table1', [Date] = _MAXDate))

     

    Output:

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    MuppetyMe ,

    In Table2, create a new calculated column

    Recent Teammate = 
    CALCULATE(
    FIRSTNONBLANK(Table1[Teammate], 1),
    FILTER(
    Table1,
    Table1[ID] = Table2[ID] &&
    Table1[Date] = CALCULATE(MAX(Table1[Date]), Table1[ID] = Table2[ID])
    )
    )