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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jk8979356
Regular Visitor

How should I design relationships between 3 tables?

Forgive me as I don't fully understand the proper way to design table relationships. I have three tables of transaction data: 2019 Actual Sales, 2020 Sales Forecast, 2021 Sales Forecast. Each table has the same 31 data columns, except the 2020 Sales Forecast table which has an additional 2 calculated columns. These are tables full of transactional data with no unique identifier keys. I am trying to use Power Query / Power Pivot to combine these 3 tables into a single pivot with multiple data rows that each use the Quantity & Revenue columns for the pivot table "Values". How do I create the relationships so I can compare Values from the 3 tables for the common columns shown here. Keeping in mind each table has thousands of different entries for each data row type. 

jk8979356_0-1602448238510.png

 

1 ACCEPTED SOLUTION
rh203
Helper I
Helper I

Hi,

 

My personal view on this is that you will come unstuck somewhere without a dimension table of some kind to at least link as a one to many relationship between the various FACT tables you have.


For Power Pivot:

If it were me I would do the following:

 

1) Create a new table by appending all of the sales actual / forecast tables together and then removing the transactional data columns to just leave the 'dimension' elements such as 'Customer ID' / 'Customer Address' etc etc. Preferably do the same repeatedly until you create a dimension table for the various types of dimensions you have (eg a customer master table, an item table). Make sure that you use 'remove duplicates' on the key field in each of these dimension tables so that you end up with only one instance of each customer / item etc etc.

 

2) I would then personally merge the Forecast 2020 and 2021 tables together to create a single 'Forecast' FACT table and leave the 2019 actual a as the sales 'Actual' FACT table.

 

3) Create a relationship from the dimension tables from step 1 to the FACT tables created in step 2 using one-to-many relationships from a key field (e.g customer ID) to the same field in the two FACT tables.

 

Power Pivot can have some strange behaviour in my experience unless you follow the straight forward Dimension (one) to a FACT [transactional data] (many) setup. It doesn't support many to many relationships and I have found out myself the hard way that you can end up on a wild goose chase unless you keep it simple.

 

In all honesty, if all you are trying to achieve is to show all of the results together so you can slice and dice as you please, you will probably be best off appending all tables together with the addition of a simple 'date' column in power query, outputting this as a basic table in excel and then reading that table with a normal pivot table (not power pivot)

 

hope that helps

 

Ryan

View solution in original post

1 REPLY 1
rh203
Helper I
Helper I

Hi,

 

My personal view on this is that you will come unstuck somewhere without a dimension table of some kind to at least link as a one to many relationship between the various FACT tables you have.


For Power Pivot:

If it were me I would do the following:

 

1) Create a new table by appending all of the sales actual / forecast tables together and then removing the transactional data columns to just leave the 'dimension' elements such as 'Customer ID' / 'Customer Address' etc etc. Preferably do the same repeatedly until you create a dimension table for the various types of dimensions you have (eg a customer master table, an item table). Make sure that you use 'remove duplicates' on the key field in each of these dimension tables so that you end up with only one instance of each customer / item etc etc.

 

2) I would then personally merge the Forecast 2020 and 2021 tables together to create a single 'Forecast' FACT table and leave the 2019 actual a as the sales 'Actual' FACT table.

 

3) Create a relationship from the dimension tables from step 1 to the FACT tables created in step 2 using one-to-many relationships from a key field (e.g customer ID) to the same field in the two FACT tables.

 

Power Pivot can have some strange behaviour in my experience unless you follow the straight forward Dimension (one) to a FACT [transactional data] (many) setup. It doesn't support many to many relationships and I have found out myself the hard way that you can end up on a wild goose chase unless you keep it simple.

 

In all honesty, if all you are trying to achieve is to show all of the results together so you can slice and dice as you please, you will probably be best off appending all tables together with the addition of a simple 'date' column in power query, outputting this as a basic table in excel and then reading that table with a normal pivot table (not power pivot)

 

hope that helps

 

Ryan

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.