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]

Just to be sure: the formulas I provided are for measures, not for calculated columns.

When I put them in a pivot table with the [Employee Name] field, I get the following results:

[TABLE="width: 356"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]# Preparer[/TD]
[TD]# Reviewer[/TD]
[/TR]
[TR]
[TD]Adatia, Jagruthi[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali, Shaheen[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Archana, Dasari[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Arcot, Shruthi Mohan[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Balaraman, Arun[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Battula, Prasanna[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Total général[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]



With the [Supervisior] field, I get the following:
[TABLE="width: 356"]
<tbody>[TR]
[TD]Supervisor[/TD]
[TD]# Preparer[/TD]
[TD]# Reviewer[/TD]
[/TR]
[TR]
[TD]Bandanakanti,Srinivas[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Dharbavenkata,Surya Narayana[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Duvva,Sunil[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Garla,Sai Prakash[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Tholeti,Pradeep Kumar[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total général[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[Mavericks334]

How do i get the supervisors Name, Because at the end of the day i need to calculate what is the productive time spent by each supervisor, which includes all the activities.

 

[Mavericks334]

It worked by using it as a measure. How do i create a measure that would help me get only certain criteria. For example i have cash, cost, amort and PI and dont wont PI to show in the filer but the remain items

 

[Laurent C]

Can you be more specific about the measure you want to implement?

Basically, the same technique should apply, to use the inactive relationship with other measures.

 

[Mavericks334]

Hi Laurent

I figured it out, created a measure not to show a particular data type Like cost by filtering it out.

 

[Mavericks334]

Hi Laurent,

The above measure is working however, i dont get the exact values at the end for each person. What it does it sums the name of the reviewe beside the preparer.[TABLE="width: 338"]
<COLGROUP><COL style="WIDTH: 77pt" width=103><COL style="WIDTH: 123pt; mso-width-source: userset; mso-width-alt: 5997" width=164><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 77pt" width=103><TBODY>[TR]
[TD="class: xl65, width: 103, bgcolor: transparent"]Preparer[/TD]
[TD="class: xl65, width: 164, bgcolor: transparent"]Reviewer[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]Fund_Prep[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]Fund_Revi[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 103, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl66, width: 164, bgcolor: transparent"]Vadrevu, Ramya[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent, align: right"]2.5[/TD]
[TD="class: xl66, width: 103, bgcolor: transparent, align: right"]0.961814739[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 103, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl66, width: 164, bgcolor: transparent"]Vadrevu, Ramya[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent, align: right"]2.5[/TD]
[TD="class: xl66, width: 103, bgcolor: transparent, align: right"]0.961814739[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 103, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl66, width: 164, bgcolor: transparent"]Keligari, Raghavender[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, width: 103, bgcolor: transparent, align: right"]0.384725896[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 103, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl66, width: 164, bgcolor: transparent"]Keligari, Raghavender[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, width: 103, bgcolor: transparent, align: right"]0.384725896[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 103, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl66, width: 164, bgcolor: transparent"]Rana, Rakesh[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, width: 103, bgcolor: transparent, align: right"]0.384725896[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 103, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl66, width: 164, bgcolor: transparent"]Rana, Rakesh[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, width: 103, bgcolor: transparent, align: right"]0.384725896[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 103, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl66, width: 164, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, width: 103, bgcolor: transparent, align: right"]0.384725896[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 103, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl66, width: 164, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, width: 103, bgcolor: transparent, align: right"]0.384725896[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 260"]
<COLGROUP><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><TBODY>[TR]
[TD="class: xl65, width: 144, bgcolor: #4f81bd"]Employee Name[/TD]
[TD="class: xl65, width: 49, bgcolor: #4f81bd"]ID[/TD]
[TD="class: xl65, width: 153, bgcolor: #4f81bd"]Supervisior[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dce6f1"]Archana, Dasari[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]113270[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]Bandanakanti,Srinivas[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Keligari, Raghavender[/TD]
[TD="class: xl69, bgcolor: transparent"]119670[/TD]
[TD="class: xl69, bgcolor: transparent"]Tholeti,Pradeep Kumar[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #dce6f1"]Rana, Rakesh[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]114357[/TD]
[TD="class: xl67, bgcolor: #dce6f1"]Bandanakanti,Srinivas[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Vadrevu, Ramya[/TD]
[TD="class: xl69, bgcolor: transparent"]120832[/TD]
[TD="class: xl69, bgcolor: transparent"]Tholeti,Pradeep Kumar[/TD]
[/TR]
</TBODY>[/TABLE]


It should Pick the supervisors information from this table for both preparer and reviewer and in the pivot

should Give me info like this[TABLE="width: 299"]
<COLGROUP><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6253" width=171><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY>[TR]
[TD="class: xl65, width: 171"]Supervisor[/TD]
[TD="class: xl65, width: 144"]Analyst[/TD]
[TD="class: xl65, width: 84"]Total[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bandanakanti,Srinivas[/TD]
[TD="class: xl65, bgcolor: transparent"]Archana, Dasari[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11.76945179[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Rana, Rakesh[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.769451791[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bandanakanti,Srinivas Total[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12.53890358[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tholeti,Pradeep Kumar[/TD]
[TD="class: xl65, bgcolor: transparent"]Keligari, Raghavender[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.769451791[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Vadrevu, Ramya[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1.923629478[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tholeti,Pradeep Kumar Total[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2.69308127[/TD]
[/TR]
[TR]
[TD="class: xl65"]Grand Total[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]15.23198485[/TD]
[/TR]
</TBODY>[/TABLE]

which is not happening it adds the values beside to it.

 

[Laurent C]

Can you specify the exact measure definition you are referring to?

 

[Mavericks334]

Hi Laurent,

I am trying to find out what is the total time spent by each person irrespective whether they do a preparation activity or review activity. I wish to combine both their times. And also the overall time of a supervisor. The supervisor could either be same/different for the person who is preparing and reviewing the activities.
The Table would have
PReparer Name Reviver name Task I need to look up the supervisors name from a different sheet and then create a pivot with Supervisor and the his team.

 

[Laurent C]

Once you have defined your measures, say [Preparation Time] and [Reviewing Time], you will just have to define a new measure
[Total Time] := [Preparation Time] +[Reviewing Time]

[Reviewing Time] being defined by using USERELATIONSHIP, as already done before for the number of activities.

Since Supervisor is already an attribute of your employee table, this should not be an issue.

 

[Mavericks334]

I have tried it and it is not working.