Creating a column that shows related record ID

[Arete]

Hi, I'm stuck on a problem and love that there's a community that can help!

I have a table that contains a column of related record IDs

ID	Team1_ID
Project_01	Project_03
Project_02
Project_03

I'd like to create a new column that shows the inverse, for example:

ID	Team1_ID	Desired Column
Project_01	Project_03
Project_02
Project_03		Project_01

I'm pretty new to DAX so any suggestions are appreciated!

[johnt75 · Accepted solution]

If you know that there will only ever be 1 entry for an ID in the Team 1 ID column then you could use

Desired Column =
VAR CurrentID = 'Table'[ID]
RETURN
    LOOKUPVALUE ( 'Table'[ID], 'Table'[Team 1 ID], CurrentID )

[Arete]

This worked perfectly! Thanks for the help 🙂

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Desired CC =
MAXX ( FILTER ( Data, Data[Team1_ID] = EARLIER ( Data[ID] ) ), Data[ID] )