LOOKUPVALUE troubles

[MuppetyMe]

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. 

[Kedar_Pande]

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

[Anonymous]

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.

[SachinNandanwar]

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

 

[parry2k]

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

[MuppetyMe]

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 · Accepted solution]

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

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