Display multiple fields that reference 1 master table

[gancw1]

I have 2 tables

Customer Table

Company	Sales Mgr	Acc Mgr	Support Mgr
Comp A	1	4	4
Comp B	2	3	3
Comp C	1	4	3

Staff Table

ID	Name
1	James H
2	Tan KK
3	June J
4	Dave O

How can I display the cutomer table showing the person's name instead of the ID?
Is there a way to do this in Power BI without merging the tables or do I have to
- merge the customer table with the staff table three times in Power Query, or
- make 3 copies of the staff table and link them to the customer table ?

Thanks

[Anonymous]

Hi @gancw1 ,

Did Irwan 's suggestion help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If that also doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Irwan]

hello @gancw1 

i think it is possible with a little tweak.

1. unpivot your 'Customer' Table.

2. create a relationship many-to-many between 'Customer' table and 'Staff' table.

3. plot in matrix visual with Values taken from 'Name' column in 'Staff' table

Hope this will help.

Thank you.

[gancw1 · Accepted solution]

Thanks. Unpivot does the trick ! 

[rubayatyasmin]

Hi, @gancw1 

I'm not sure I understand it correctly. But what I am guessing is that mngrAcc and MngrSupport are the IDs that match the IDs in the Staff table, right?

If yes, and you do not want to merge, then you should create a relationship using the three key columns in the customer and Staff tables. One will be active, and the other two will be inactive. 

Then, in DAX you have use UseRelationship DAX func to utilize the relationship and get the result. 

refer: USERELATIONSHIP with TEXT - Microsoft Fabric Community

[gancw1]

for this method do I use UseRelationship to create new computed columns ?