Concatenate values in distant related table

[BlueTiger]

I need to create a new column in the Clients table below which has the Staff manager's first name appended to the clients display name.

i.e. (Joe) ABC Company Ltd

Staff table is related to Relationship 1:* table by their StaffID for manager/partner/team.

Relationship table is related to the Clients table by ClientID 1:1.

I know how to use the concatenate command, but how do I reference the other table? When I use RELATED() it only allows me to see the Relationship table.

I know I could merge Relationship and Clients table, but I am avoiding that at the moment due to external issues.

[Nathaniel_C]

Hi @BlueTiger ,
If I understand correctly, as you are going from the 1 to * you will probably need to use the RELATEDTABLE().
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[BlueTiger]

I can't seem to reference the Column [First Name] when I do that. I am using CALCULATEDTABLE instead of RELATEDTABLE as I need to specify the relationship to use and RELATEDTABLE is just a short hand version of CALCULATEDTABLE.

Staff with Client Name = "(" & 
    CALCULATETABLE(
        Staff, 
        USERELATIONSHIP(
            Staff[StaffID],
            Relationship[Manager_StaffID]
        )
    )[First Name]
    & ") " & Clients[Display Name]

[First Name] is greyed out so it obviously isn't expecting me to specify the column there, but I am not sure why? It gives the error:

The syntax for '[First Name]' is incorrect. (DAX("(" & CALCULATETABLE( Staff, USERELATIONSHIP( Staff[StaffID], Relationship[Manager_StaffID] ) )[First Name] & ") " & Clients[Display Name])).

[Icey]

Hi @BlueTiger ,

If you don't mind, please share me a dummy PBIX file without real data and sensitive information.

Best Regards,

Icey

[BlueTiger]

Thank you.

I have created an example pbix file below with random generated data. This is a snippet of a larger data model I am working on.

https://www.dropbox.com/s/6irl43g5wyrselg/Forum_HelpWithRELATEDTABLE.pbix?dl=1

FYI In my first post I accidentally put the 1:* relationships the wrong way round from Staff to Relationship. I didn't notice until I started entering data for the example file.

[Icey · Accepted solution]

Hi @BlueTiger ,

Try this:

ManagerFirstName =
CALCULATE (
    CALCULATE ( VALUES ( Staff[First Name] ), Relationship ),
    USERELATIONSHIP ( Staff[StaffID], Relationship[Manager_StaffID] ),
    ALL ( Staff )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.