Forum Discussion

jbrown's avatar
jbrown
New Member
9 years ago
Solved

Multiple table unions and joins

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's avatar
    v-qiuyu-msft
    9 years ago

    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

3 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    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's avatar
      jbrown
      New Member
      9 years ago

      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's avatar
        v-qiuyu-msft
        Icon for Community Support rankCommunity Support
        9 years ago

        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