Many to Many Relationship between same two tables- Related function not using the second relationship

[Mavericks334]

Hi,

I have a Tables called Data & Employee.

The common Field is the employee Name.

In the Data Table, i have Preparer and Reviewer in two seperate Columns, and in the Employee i have the name of the employee and supervisor.

I need to get the supervisors name for both the preparer and reviewer. When i use related it gives me the only the supervisors for Preparer.

How could i get the supervisors name for the reviewer.

There is a relationship between Preparer and Employee and Reviwer and Employee.

Regards,
Renato.

 

[Laurent C]

I assume you have your Employee table as follows:
Employee {ID, Employee Name, Supervisor}

Your table with your data:
Data {Preparer, Reviewer, ...}


And you have the appropriate active (Preparer) and inactive (Reviewer) relationships.

Measure As Preparer:=YourExpression

Measure As Reviewer:=CALCULATE(
YourExpression
,USERELATIONSHIP(Data[Reviewer],Employee[Employee Name])
)

Measure in all roles:= [Measure As Preparer] + [Measure As Reviewer]
Now, the following should look fine:
Employee[Employee Name], [Measure As Preparer],[Measure As Reviewer]

This should also work for:
Employee[Supervisor], [Measure Preparer],[Measure Reviewer]

You should not try to add a calculated column to your Data table to do that. This is what your dimensions are for.