Relationships between tables

[Anonymous]

Hi,

I have 5 data tables. I have previously been using 4 tables which had been working fine with all the relationships between them, but now I have added the 5th I am having trouble using the data as I would like with that table.

Table 1 - Customers (unique Customer ID)

Table 2 - Contacts (unique Contact ID + corresponsding Customer ID)

Table 3 - Activity (unique Activity ID + corresponding Contact ID)

Table 4 - Important Activity (Activity ID (subset of Activity IDs in Table 3) + other irrelevant columns including Activity Type)

Table 5 (new) - Meetings (unique Meeting ID + corresponding Activity ID (subset of Activity IDs in Table 3) + other irrelevant columns)

The only relationships between the tables are:

Table 1 - 1 to many relationship with Table 2 (key: customer ID)

Table 2 - 1 to many relationship with Table 3 (key: contact ID)

Table 3 - 1 to 1 relationship with Table 4 (key: activity ID)

Table 3 - 1 to 1 relationship with Table 5 (key: activity ID)

What I would like to be able to do is create tables in the report such as:

Customer ID (Table 1) || Count of Meeting ID (Table 5)

*This works with Customer ID (Table 1) || Count of Activity ID (Table 4) - so why does the above not work?*

And I would like to be able to create e.g. pie chart in the report showing:

Legend: Activity Type (Table 3)

Values: Count of Meeting ID (Table 5)

Examples of the tables:

Table 1:

Customer ID

Customer 1

Customer 2

Customer 3

Table 2:

Contact ID	Customer ID
Contact 1	Customer 1
Contact 2	Customer 1
Contact 3	Customer 2
Contact 4	Customer 3
Contact 5	Customer 3

Table 3:

Activity ID	Contact ID
Activity 1	Contact 1
Activity 2	Contact 2
Activity 3	Contact 2
Activity 4	Contact 3
Activity 5	Contact 5
Activity 6	Contact 5

Table 4:

Activity ID	Activity Type	+Other columns
Activity 1	Type 1
Activity 2	Type 2
Activity 4	Type 1
Activity 5	Type 2
Activity 6	Type 3

Table 5:

Meeting ID	Activity ID
Meeting 1	Activity 1
Meeting 2	Activity 4
Meeting 3	Activity 6

[v-piga-msft]

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your desired output.

Best  Regards,

Cherry

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

---

What I would like to be able to do is create tables in the report such as:

Customer ID (Table 1) || Count of Meeting ID (Table 5)

*This works with Customer ID (Table 1) || Count of Activity ID (Table 4) - so why does the above not work?*

And I would like to be able to create e.g. pie chart in the report showing:

Legend: Activity Type (Table 3)

Values: Count of Meeting ID (Table 5)

---

By my test with your sample data, do you want to create the visual like below?

If that is your desired output, please create the relationship like below.

If you still need help, please share your desired output so that I could understand your requirement better.

Best  Regards,

Cherry