Forum Discussion
Managing relationships between two data sources
Hi qpr4leyf ,
If you encounter a many-to-many relationship warning, it means that there are non-unique values in the column you are trying to join. This occurs when you have multiple data for the same date/country.
Consider creating a date dimension table to resolve this.
Please follow these steps:
1. Here's the example data I created:
Date of Purchase
Country
CustomerID
1/1/2024
A
a1
2/1/2024
B
a2
3/1/2024
C
a3
4/1/2024
D
a4
5/1/2024
E
a5
1/1/2024
A
a6
4/1/2024
B
a7
1/1/2024
C
a8
4/1/2024
D
a9
4/1/2024
E
a10
Date of Purchase
Country
Sales
1/1/2024
A
100
2/1/2024
B
200
3/1/2024
C
300
4/1/2024
D
400
5/1/2024
E
500
1/1/2024
A
100
2/1/2024
B
200
3/1/2024
C
300
4/1/2024
D
400
5/1/2024
E
500
2. Create a date table:
Date = CALENDAR(MIN('Table'[Date of Purchase]),MAX('Table'[Date of Purchase]))3. Building Relationships:
4. Drag to the chart for display:
5. The final page effect is as follows:
If you have any other questions please feel free to contact me.
The pbix file is attached.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!Hi qpr4leyf ,
When there are no unique values in either table, *:* will be displayed after the relationship is established.
At this time, it is best to create a dimension table, such as the date dimension table in my reply.
Establish a relationship between the fact table and the dimension table. Since the date column in the date dimension table is unique, it is used as a connecting field to establish a relationship with the two tables. At this time, the most ideal 1:* relationship is established.
If you want to learn more about fact tables and dimension tables, please refer to:
How to Create Fact and Dimension Tables in Power BI - Zebra BI
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hi qpr4leyf ,
If you encounter a many-to-many relationship warning, it means that there are non-unique values in the column you are trying to join. This occurs when you have multiple data for the same date/country.
Consider creating a date dimension table to resolve this.
Please follow these steps:
1. Here's the example data I created:
|
Date of Purchase |
Country |
CustomerID |
|
1/1/2024 |
A |
a1 |
|
2/1/2024 |
B |
a2 |
|
3/1/2024 |
C |
a3 |
|
4/1/2024 |
D |
a4 |
|
5/1/2024 |
E |
a5 |
|
1/1/2024 |
A |
a6 |
|
4/1/2024 |
B |
a7 |
|
1/1/2024 |
C |
a8 |
|
4/1/2024 |
D |
a9 |
|
4/1/2024 |
E |
a10 |
|
Date of Purchase |
Country |
Sales |
|
1/1/2024 |
A |
100 |
|
2/1/2024 |
B |
200 |
|
3/1/2024 |
C |
300 |
|
4/1/2024 |
D |
400 |
|
5/1/2024 |
E |
500 |
|
1/1/2024 |
A |
100 |
|
2/1/2024 |
B |
200 |
|
3/1/2024 |
C |
300 |
|
4/1/2024 |
D |
400 |
|
5/1/2024 |
E |
500 |
2. Create a date table:
Date = CALENDAR(MIN('Table'[Date of Purchase]),MAX('Table'[Date of Purchase]))
3. Building Relationships:
4. Drag to the chart for display:
5. The final page effect is as follows:
If you have any other questions please feel free to contact me.
The pbix file is attached.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Thank you for helping Anonymous . Seems quite complex to create relationships in Tableau if you want to create a relationship between multiple fields. I was hoping that I would be able to easily define relationships between multiple columns that exist between the datasets I am using for my analysis, but it looks like I have to create multiple new tables to be able to achieve these relationships.
Imagine you have two tables, one that is pretty granular with item level detailes.
E.g. Date, Time of purchase, Country, City, Store, Item, Purchase Status, Price in USD in Table 1
You want to join this with a more high level dataset that displays
Date, Country, Store, Date, Country, City, Store, Transactions in Table 2
In my world I'd just drag drop the fields I want to join in the Model view, e.g. Date, Country, City, Store.
Then I should be able to sum up the Price in USD from Table 1 in a visual next to Transactions from Table 2
If I understand this correctly, this does not seem possible without adding multiple additional tables, and it easily gets super complex to achieve quite simple things?
Hi qpr4leyf ,
When there are no unique values in either table, *:* will be displayed after the relationship is established.
At this time, it is best to create a dimension table, such as the date dimension table in my reply.
Establish a relationship between the fact table and the dimension table. Since the date column in the date dimension table is unique, it is used as a connecting field to establish a relationship with the two tables. At this time, the most ideal 1:* relationship is established.
If you want to learn more about fact tables and dimension tables, please refer to:
How to Create Fact and Dimension Tables in Power BI - Zebra BI
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
