Managing relationships between two data sources

[qpr4leyf]

Hi,

I am new to PowerBI and I'm struggling, despite watching several tutorials. The tutorials generally are trouble free because they demo very simple dataset relationships. I am basically after creating some sort of light join that allows me to join by two fields (in the below case Date and Country, to display a combo chart where I display Spend per Date/Country and a line on my secondary axis that shows the number of different customers for that Date/Country selection. Maybe Relationships aren't built for this purpose? If not, how else can I achieve this? In Tableau "relationships" do exactly the above, but in PowerBI it just seems overly complex for a newbie.

I have two datasets and I'd like to create a relationship between them. PS the actual datasets contain some more fields, but there are simplified versions to display what I want to achieve.

Dataset 1:

Date of Purchase

Customer ID

Country

City

Dataset 2:

Date of Purchase

Country

Spend

Transactions

The expected utcome is that I can create a combination chart where I join the data in these two tables. For example I'd like to show the number of distinct customers that made a purchase on a given day per Country as a stacked column chart, and the total Spend as a line on my secondary axis.

My simple mind would think that I'd simply create many-to-many relationships between Date of Purchase and Country to achieve this, but I get warning messages every time and my secondary axis remains a flat line as no relationships are formed. In both of my datasets I have multiple rows for every single value, because I have many dates/country combiations in one dataset and even more date/country/customer combinations in my second dataset.

An example of an error that I get:

"This relationship has cardinality Many-Many. This should only be used if it is expected that neither column (Country and Country) contains unique values, and that the significantly different behaviour of Many-Many relationships is understood"

Could someone guide me on how to make the above chart as an example, and maybe I will understand a bit more?

[Anonymous · Accepted solution]

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!

[Anonymous · Accepted solution]

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!

[qpr4leyf]

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?

[Anonymous · Accepted solution]

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 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!