Forum Discussion

MuppetyMe's avatar
MuppetyMe
Icon for Helper I rankHelper 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. Cr...
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] 
    )
)

     

     

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.