Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
Proud to be a Super User!
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:
Hi @BlueTiger ,
If you don't mind, please share me a dummy PBIX file without real data and sensitive information.
Best Regards,
Icey
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
104 | |
87 | |
73 | |
66 |
User | Count |
---|---|
119 | |
111 | |
95 | |
79 | |
72 |