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

     

     

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper 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])
)
)