Forum Discussion

DerMoe's avatar
DerMoe
Frequent Visitor
4 years ago
Solved

Join a table several times

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:

UserNameUser ID
Maike1
Susi2


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

ObjectCalledByCanceldByStartedByTestedBy
A1122
B2121

 

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

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    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

1 Reply

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    4 years ago

    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