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]

Can you elaborate on the relationships between Preparer / Reviewer and Employees?

 

[Mavericks334]

Below are the details of the table

Employee Table:

[TABLE="width: 294"]
<COLGROUP><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7424" width=203><TBODY>[TR]
[TD="class: xl65, width: 140, bgcolor: #4f81bd"]Employee Name[/TD]
[TD="class: xl66, width: 49, bgcolor: #4f81bd"]ID[/TD]
[TD="class: xl67, width: 203, bgcolor: #4f81bd"]Supervisior[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Abhiraj, Santosh[/TD]
[TD="class: xl69, bgcolor: #dce6f1"]121366[/TD]
[TD="class: xl70, bgcolor: #dce6f1"]Garla,Sai Prakash[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]Adatia, Jagruthi[/TD]
[TD="class: xl72, bgcolor: transparent"]123681[/TD]
[TD="class: xl73, bgcolor: transparent"]Garla,Sai Prakash[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Ali, Shaheen[/TD]
[TD="class: xl69, bgcolor: #dce6f1"]123694[/TD]
[TD="class: xl70, bgcolor: #dce6f1"]Tholeti,Pradeep Kumar[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl72, bgcolor: transparent"]113270[/TD]
[TD="class: xl73, bgcolor: transparent"]Bandanakanti,Srinivas[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Arcot, Shruthi Mohan[/TD]
[TD="class: xl69, bgcolor: #dce6f1"]123683[/TD]
[TD="class: xl70, bgcolor: #dce6f1"]Duvva,Sunil[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]Azmi, Sobia[/TD]
[TD="class: xl72, bgcolor: transparent"]121302[/TD]
[TD="class: xl73, bgcolor: transparent"]Dharbavenkata,Surya Narayana[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Balaraman, Arun[/TD]
[TD="class: xl69, bgcolor: #dce6f1"]123662[/TD]
[TD="class: xl70, bgcolor: #dce6f1"]Dharbavenkata,Surya Narayana[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]Battula, Prasanna[/TD]
[TD="class: xl72, bgcolor: transparent"]112537[/TD]
[TD="class: xl73, bgcolor: transparent"]Garla,Sai Prakash[/TD]
[/TR]
</TBODY>[/TABLE]


Data Table:
[TABLE="width: 369"]
<COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7424" width=203><TBODY>[TR]
[TD="class: xl71, width: 160, bgcolor: #8db4e2"]Preparer[/TD]
[TD="class: xl71, width: 129, bgcolor: #8db4e2"]Reviewer[/TD]
[TD="class: xl71, width: 203, bgcolor: #8db4e2"]Recon Name[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1"]Adatia, Jagruthi[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]Archana, Dasari[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]POZ Cash Management 16039[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Adatia, Jagruthi[/TD]
[TD="class: xl69, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl70, bgcolor: transparent"]POZ Cash Management 13054[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1"]Adatia, Jagruthi[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]Archana, Dasari[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]POZ Cash Management 16040[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Adatia, Jagruthi[/TD]
[TD="class: xl69, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl70, bgcolor: transparent"]16243_Cash[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1"]Adatia, Jagruthi[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]Archana, Dasari[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]16243_PI[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Ali, Shaheen[/TD]
[TD="class: xl69, bgcolor: transparent"]Battula, Prasanna[/TD]
[TD="class: xl70, bgcolor: transparent"]15849_Cash[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1"]Ali, Shaheen[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]Battula, Prasanna[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]15848_P and I[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Ali, Shaheen[/TD]
[TD="class: xl69, bgcolor: transparent"]Battula, Prasanna[/TD]
[TD="class: xl70, bgcolor: transparent"]15849_P and I[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1"]Ali, Shaheen[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]Battula, Prasanna[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]15848_Cash[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Battula, Prasanna[/TD]
[TD="class: xl69, bgcolor: transparent"]Arcot, Shruthi Mohan[/TD]
[TD="class: xl70, bgcolor: transparent"]POZ_INT_11 OEIC[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1"]Battula, Prasanna[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]Arcot, Shruthi Mohan[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]POZ COST_11 OEIC[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Battula, Prasanna[/TD]
[TD="class: xl69, bgcolor: transparent"]Arcot, Shruthi Mohan[/TD]
[TD="class: xl70, bgcolor: transparent"]15847_PI[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1"]Archana, Dasari[/TD]
[TD="class: xl66, bgcolor: #dce6f1"]Balaraman, Arun[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]15847_Cash[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl69, bgcolor: transparent"]Balaraman, Arun[/TD]
[TD="class: xl70, bgcolor: transparent"]12513_PI[/TD]
[/TR]
</TBODY>[/TABLE]


If you notice there are there columns with Preparer, Reviewer and Recon.

For every task that is prepared by a person it is reviewed by a different person, who could be from the same time or from a different team. Currently i have relationship between the employee table and the data table for both the columns from data table to get the name for the supervisors.

 

[Laurent C]

When you talk about 2 relationships, do you mean "one active and one inactive"?

In this case you would use USERELATIONSHIP(DATA[Reviewer], Employee[Employee Name]) in a CALCULATE statement.

 

[Mavericks334]

Yes, that is correct

 

[Mavericks334]

I tired the above formula it did not work.

 

[Laurent C]

Compare the measures:

# Preparer:=COUNTA(Data[Preparer])

# Reviewer:=CALCULATE(
COUNTA(Data[Reviewer]),
USERELATIONSHIP(Data[Reviewer],Employee[Employee Name])
)

 

[Mavericks334]

To get the supervisors name for Preparer i Used Related Function it worked fine.

Since for the reviewer the relationship is inactive it is not working.

I tried the above formula it got an error stating User relationship function only accepts a column reference as the arguments number 1.

 

[Mavericks334]

To get the supervisors name for Preparer i Used Related Function it worked fine.

Since for the reviewer the relationship is inactive it is not working.

I tried the above formula it got an error stating User relationship function only accepts a column reference as the arguments number 1.

 

[Mavericks334]

I made the necessary change the formula worked, however, it does not give me the name of the supervisor, i get all blanks.