Solved: Creating a column that shows related record ID

Arete · ‎11-17-2022

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

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

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

johnt75 · ‎11-17-2022

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 )

johnt75 · ‎11-17-2022

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 · ‎11-17-2022

This worked perfectly! Thanks for the help 🙂

Jihwan_Kim · ‎11-17-2022

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Creating a column that shows related record ID