Relationship between 2 columns in each table

[Jitka]

Hello

unsuccessfully tried to create relationships. I have 2 different table about say motivation and a table about risk. Both tables have a column with ID and also reporting period. I need to create a report where I can use data from both motivation and risk, and link the total value of both and map it by ID & also the reporting period. So I matched the the columns called ID but it doesn't link reporting period, when I tried again I keep getting a message if I am really sure about creating many to many. I have no idea, please can anyone help me with this?
What type of relationship do I need to create please?
Thank you

 

[arunsjain]

Hi,

One to many relationship is advisable.

you can create two table with unique values like date table (like "dim_date") and unique id table (like "dim_ID").

Create relationship (one to many) between Like
a) dim_date table and motivation table
b) dim_date table and risk table
c) dim_ID table and motivation table
d) dim_ID and risk table

Make sure dim_date and dim_ID should have unique values and no duplicates in those tables.

Cheers!!!

 

[Jitka]

Hi,

One to many relationship is advisable.

you can create two table with unique values like date table (like "dim_date") and unique id table (like "dim_ID").

Create relationship (one to many) between Like
a) dim_date table and motivation table
b) dim_date table and risk table
c) dim_ID table and motivation table
d) dim_ID and risk table

Make sure dim_date and dim_ID should have unique values and no duplicates in those tables.

Cheers!!!

Hi, firstly I am so sorry for not reading this sooner as have been on annual leave and kids holiday so not time for anything! Thank you so much for the advise. I now realise I think the issue is that my tables have multiple values of the same ID, because they contain data from various quarters, for example client ID0000 might have 10 rows in 1 table and a similar number in the other table. I feel I need to do some training/ video watching to learn more about relationships as really struggle with this in excel or power BI. So in my case is it still one to many or many to many? Thanks so much.

 

[arunsjain]

Hi, firstly I am so sorry for not reading this sooner as have been on annual leave and kids holiday so not time for anything! Thank you so much for the advise. I now realise I think the issue is that my tables have multiple values of the same ID, because they contain data from various quarters, for example client ID0000 might have 10 rows in 1 table and a similar number in the other table. I feel I need to do some training/ video watching to learn more about relationships as really struggle with this in excel or power BI. So in my case is it still one to many or many to many? Thanks so much.

Hi, I have never tried many to many relationship. You can give a go, see if model gives the desired result. If not then you can apply step I mentioned in my previous post. You can create Dim_ID table from your table like reference to the original table, remove all other columns except ID then remove all duplicates. That will provide you a unique ID table you can name it as "Dim_ID" table. Dim means dimension table where values are unique. I hope it will help.

 

[Jitka]

Hi, I have never tried many to many relationship. You can give a go, see if model gives the desired result. If not then you can apply step I mentioned in my previous post. You can create Dim_ID table from your table like reference to the original table, remove all other columns except ID then remove all duplicates. That will provide you a unique ID table you can name it as "Dim_ID" table. Dim means dimension table where values are unique. I hope it will help.

Thanks, I need to keep all the IDs even if they are counting as duplicates as they will have unique data in various columns I need to analyse.

 

[arunsjain]

Thanks, I need to keep all the IDs even if they are counting as duplicates as they will have unique data in various columns I need to analyse.

In your original table you will have all ID including duplicates. You can create a separate table for IDs (Dim_ID) from your original table. Later link Dim_ID table with your original data table with one to many relationship.

 

[Jitka]

In your original table you will have all ID including duplicates. You can create a separate table for IDs (Dim_ID) from your original table. Later link Dim_ID table with your original data table with one to many relationship.

Ah ok thanks, that make sense. I'll try that. Thank you.