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: UserName User ID ...
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
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