Multiple table unions and joins

[jbrown]

I believe this is not too difficult but I have not been able to wrap my head around the logic to do this. I believe what I need to do is to create a calculated table.

Here's my scenario: I need to be able to count "participation" by organizations. I need to be able to display content in 1 table to show participation.

There are 3 columns:

1. Column A contains all organization data (Column B and Column C are a "many to one" relationship with Column A)

        Organization Name | City | Country | ETC

1. Column B "Meeting Attendance" contains a new row for each entry (like school attendance but for adults :))
   
   Fiscal Year | Name |Organization Name | Session
   
   
2. Column C is "Survey Participation". Just different Metrics. Here I'm only interested to see that they participated or not (I don't care yet in this view about the info). There is a new row for each entry:

        Fiscal Year | Organization Name | Survey Participation

What I want to be able to do is to build a table like this. B (meeting attendance) and C (survey participation) are already related to Column A via "Organization Name". I believe I just have to count rows for Column B and C.

Fiscal Year | Organization Name | Meeting Attendance | Survey Participation

FY17          | Acme Inc                 |   4                               |   1

FY17          | Company 2             |   0                               |   4

[v-qiuyu-msft · Accepted solution]

Hi @jbrown,

You can create a table with the DAX below:

Table = SUMMARIZE('Table1','Table1'[Organization Name],"Meeting Attendance",COUNT(Table2[Session]),"Survey Participation",COUNT('Table3'[Survey Participation]))

As you didn't provide any sample data, I created a pbix file with some dummy data. You can download it below to see if it meet your requirements.

Best Regards,
Qiuyun Yu

[jbrown]

This works.

I also need to solve for instances where records don't have "Meeting Attendance" or "Survey Participation"

For example: Table1 contains all organizations

Table2 (meeting attendance) only has records if the organization attended meeting

Table 3 (survey participation) only has records if organization took the survey.

[v-qiuyu-msft]

Hi @jbrown,

As this issue is different from the original one, please create a new thread and share sample data to clarify the requirement. So we can find the solution promptly.

Best Regards,
Qiuyun Yu