Connecting 3 fact tables by one dim Table Display in matrix

[jasenMOD]

I've been racking my brain and the internet, but cant get this to work, although im sure there is a simple solution. 

I have three fact tables: 

• SurveyTable
  • Survey Results
    • NPSGroup
• ITCTAble(Intent to cancel)
  • Table of each instance of a clients intent to cancel
    • IntentToCancelReason
• TasksAndEventsTable
  • Tasks and events between a contact and a user
    • MeetingName

Each of these contains tables has a ContactID column and i have connected each of them to the ContactTable through ContactID.

In the report builder I would like to be able to summarize these 3 different kinds of customer interaction in one Matrix. For example show contact x submitted a survey with an nps rating of 2, then later submitted an intent to cancel thier service, then the contact has a meeting with an account manager. 

When i put ContactName from ContactTable on a matrix with the following feilds I get a "Cant Determine Relationship Error"

• NPSGroup from SurveyTable
• IntentToCancelReason from ITCTable
• Meeting Name from TasksAndEventsTable

[v-zhouwen-msft · Accepted solution]

Hi @jasenMOD ,

Regarding your question, it should be a mismatch of rows between tables. For example, suppose 'ConTact ID' is 1001, and there are 2 rows in 'Survey Table' and 1 row in 'ITCTable'.

Use the following DAX expression to create a measure

CONCATENATEX function (DAX) - DAX | Microsoft Learn

_NpsScpre = CONCATENATEX('Survey Table',[NpsGroup],",")

Final output

[jasenMOD]

Can i then do the same thing for the tasks and events table?

[v-zhouwen-msft]

Hi @jasenMOD ,

Regarding your question, I don't think there is a problem. All three fact tables look up the corresponding data by column 'Contact ID' and use the 'CONCATENATEX' function to merge multiple rows of data into a single cell.