Join a table several times

[DerMoe]

Hi Gyus,

I think I'm not the first with such a question but I could not find a suitable post!

I have a table with all users incl. information which is similar to this:

UserName	User ID
Maike	1
Susi	2

Now I have a table where I have to reference the user more times:

Object	CalledBy	CanceldBy	StartedBy	TestedBy
A	1	1	2	2
B	2	1	2	1

However, I can only create a reference once, because the second time I can only create an inactive reference to user table.

Is there now a "smart" solution for this problem or do I have to create a separate user table for each column where I need a user.

Thank you very much!

Greetings

[jdbuchanan71 · Accepted solution]

@DerMoe 

You have two options.  The first is to create the multiple relationships and activate them in your measures.  Lets say the active relation is between Table1[User ID] and Table2[CalledBy]. 

You have a measure that counts the Called By like this.

Called By = COUNTROWS ( Table2 )

Then we can also use that to count the other versions by activating an inactive link using USERELATIONSHIP

Canceled By = CALCULATE ( [Called By], USERELATIONSHIP ( Table1[User ID], Table2[CanceledBy] ) )

StartedBy = CALCULATE ( [Called By], USERELATIONSHIP ( Table1[User ID], Table2[StartedBy] ) )

Tested By = CALCULATE ( [Called By], USERELATIONSHIP ( Table1[User ID], Table2[TestedBy] ) )

Each of the measures identifies the inactive relationship to turn on to do the calculation.

The other option would be to unpivot your columns so table 2 ends up looking like this:

Object	Type	User ID
A	CalledBy	1
A	CanceldBy	1
A	StartedBy	2
A	TestedBy	2
B	CalledBy	2
B	CanceldBy	1
B	StartedBy	2
B	TestedBy	1