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

     

     

SachinNandanwar's avatar
SachinNandanwar
Icon for Impactful Individual rankImpactful 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 )
)