Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
9 years ago

Transitive Relationships

I'll try to make this coherent:

 

I have the following relationships created. I need to write measures to calculate the quantity of Notes entered by the following roles:

1. CustServiceRepID

2. PMMgrID

3. TechMgrID

 

The Notes Table has a column "CreatedByUserID" which is linked to "JMWebUsersSystemALL(UserID)"

 

I created relationships attempting to connect UserID to the role IDs listed above, but my measures all produce the same quantitities.

 

 

PrgMgr Notes = CALCULATE(COUNTA('dpmgr tblContactNotes'[Note]), 'dpmgr tblContactNotes'[IsSystem] = 0, 'dpmgr tblContactNotes'[IsAuto]= 0, USERELATIONSHIP('dpmgr tblContacts'[PMMgrID], 'dpmgr vwJMWebUsersSystemALL'[UserID]))

 

TSM Notes = CALCULATE(COUNTA('dpmgr tblContactNotes'[Note]), 'dpmgr tblContactNotes'[IsSystem] = 0, 'dpmgr tblContactNotes'[IsAuto]= 0, USERELATIONSHIP('dpmgr tblContacts'[TechMgrID], 'dpmgr vwJMWebUsersSystemALL'[UserID]))

 

COL Notes = CALCULATE(COUNTA('dpmgr tblContactNotes'[Note]), 'dpmgr tblContactNotes'[IsSystem] = 0, 'dpmgr tblContactNotes'[IsAuto]= 0, USERELATIONSHIP('dpmgr tblContacts'[CustServiceRepID], 'dpmgr vwJMWebUsersSystemALL'[UserID]))

 

But they all spit out the same values

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi bhmiller89, in your second screenshot, what is the field on Rows in your table?

    • bhmiller89's avatar
      bhmiller89
      Helper V
      9 years ago

      Anonymous I have Organization Name, TSM, PM, and COL.  Basically the roles that I need to calculate quantity for

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi bhmiller89,

         

        Could you share a sample pbix file which can reproduce the issue in this case? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.:smileyhappy:

         

        Regards