Transitive Relationships

[bhmiller89]

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

[Anonymous]

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

[bhmiller89]

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

[v-ljerr-msft]

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.

Regards

[Anonymous]

I know this is old but apparently still actual. Sharing my template (unfortunately, thanks Excel, the data model seems to be broken but at least the tables are there): https://1drv.ms/x/s!AjBnB9hwJU8wgdQ6kT6QnLaF4hTeNw?e=qlI8KO
In which we start from this model:

(colored tables being the ones with unique values/references used as key for relationships)
So desired outcome would be to have:

insted of:

Thx!

PS: Crossjoin is not a solution because it generates duplicate rows making the sum of revenue fake.