Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Relationships between tables

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 IDCustomer ID
Contact 1Customer 1
Contact 2Customer 1
Contact 3Customer 2
Contact 4Customer 3
Contact 5Customer 3

 

Table 3:

 

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

 

Table 4:

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

 

Table 5:

Meeting IDActivity ID
Meeting 1Activity 1
Meeting 2Activity 4
Meeting 3Activity 6
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

With these relationship

3.png

I can create tables in the report such as:

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

4.png

create e.g. pie chart in the report showing:

Legend: Activity Type (Table 3)

Values: Count of Meeting ID (Table 5)

5.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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)

 

Thank you in advance

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

With these relationship

3.png

I can create tables in the report such as:

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

4.png

create e.g. pie chart in the report showing:

Legend: Activity Type (Table 3)

Values: Count of Meeting ID (Table 5)

5.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.