Using inactive relationships for table's row headers

[PowerWhy]

Hello,

I have 2 tables. The first has a list of projects (with a project ID field and project manager, quality controller and credit controller fields (last 3 have employee ID's as values)). The second table is a list of employees (employee ID and employee full name fields).

I have joined the first table to the second with 3 inactive relationships:

'Projects'[Project Manager] <-> 'Employees'[Employee ID]

'Projects'[Quality Controller] <-> 'Employees'[Employee ID] 

'Projects'[Credit Controller] <-> 'Employees'[Employee ID]

Please can you tell me the simplist way to allow the user to drag and drop fields as table row headers, while allowing the user to choose what inactive relationship is used?

e.g. two users may want row headers "Project ID" and "Employee Full Name" in their tables. But one user may want to use the "Quality Controller" indirect relationship and the other user may want to use the "Credit Controller" relationship. Please can you tell me the clearest way to give this functionality?

Thank you,

PW

[Anonymous · Accepted solution]

HI @PowerWhy,

Current power bi seems not support dynamic switch between inactive relationship.

You can try to use measure expression to calculate with userelationship function to choose which relationism to use or try to use 'unpivot column' feature on your table fields at query editor side. Then you can accurately link table values and use attribute to control them.

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Unpivot columns - Power Query | Microsoft Learn

Regards,

Xiaoxin Sheng