How to create measures for data with many-to-many relationship

Anonymous — Fri, 14 Jul 2023 20:15:31 GMT

Hi everyone,

I have these two tables connected by a key column, with a many-to-many relationship,

I can't change this relationship setting, it is a limitation of my data sources,

With that established, I would like to create a measure to put into a simple Table visual, that sums the value from one table with the other table,

For example, I have this table 1:

ID TABLE 1	DATE	SHIP
A	01/05/2023	MAERSK LAMANAI
A	02/02/2023	CMA CGM RHONE
A	03/07/2023	SAN VICENTE

And this table 2:

ID TABLE 2	POD	TT
A	Tenerife	23
A	Rotterdam	28
A	Lisboa	25
A	Las Palmas	24
A	Bremerhaven	30
A	Vado Ligure	30

When I connect both tables by column ID, I get the following table when I build it using the Table Visual:

DATE	SHIP	POD	TT
01/05/2023	MAERSK LAMANAI	Tenerife	23
01/05/2023	MAERSK LAMANAI	Rotterdam	28
01/05/2023	MAERSK LAMANAI	Lisboa	25
01/05/2023	MAERSK LAMANAI	Las Palmas	24
01/05/2023	MAERSK LAMANAI	Bremerhaven	30
01/05/2023	MAERSK LAMANAI	Vado Ligure	30
02/02/2023	CMA CGM RHONE	Tenerife	23
02/02/2023	CMA CGM RHONE	Rotterdam	28
02/02/2023	CMA CGM RHONE	Lisboa	25
02/02/2023	CMA CGM RHONE	Las Palmas	24
02/02/2023	CMA CGM RHONE	Bremerhaven	30
02/02/2023	CMA CGM RHONE	Vado Ligure	30
03/07/2023	SAN VICENTE	Tenerife	23
03/07/2023	SAN VICENTE	Rotterdam	28
03/07/2023	SAN VICENTE	Lisboa	25
03/07/2023	SAN VICENTE	Las Palmas	24
03/07/2023	SAN VICENTE	Bremerhaven	30
03/07/2023	SAN VICENTE	Vado Ligure	30

I would like to create a measure that sums the column TT to the column DATE, getting the expected result:

DATE	SHIP	POD	TT	RESULT
01/05/2023	MAERSK LAMANAI	Tenerife	23	24/05/2023
01/05/2023	MAERSK LAMANAI	Rotterdam	28	29/05/2023
01/05/2023	MAERSK LAMANAI	Lisboa	25	26/05/2023
01/05/2023	MAERSK LAMANAI	Las Palmas	24	25/05/2023
01/05/2023	MAERSK LAMANAI	Bremerhaven	30	31/05/2023
01/05/2023	MAERSK LAMANAI	Vado Ligure	30	31/05/2023
02/02/2023	CMA CGM RHONE	Tenerife	23	25/02/2023
02/02/2023	CMA CGM RHONE	Rotterdam	28	02/03/2023
02/02/2023	CMA CGM RHONE	Lisboa	25	27/02/2023
02/02/2023	CMA CGM RHONE	Las Palmas	24	26/02/2023
02/02/2023	CMA CGM RHONE	Bremerhaven	30	04/03/2023
02/02/2023	CMA CGM RHONE	Vado Ligure	30	04/03/2023
03/07/2023	SAN VICENTE	Tenerife	23	26/07/2023
03/07/2023	SAN VICENTE	Rotterdam	28	31/07/2023
03/07/2023	SAN VICENTE	Lisboa	25	28/07/2023
03/07/2023	SAN VICENTE	Las Palmas	24	27/07/2023
03/07/2023	SAN VICENTE	Bremerhaven	30	02/08/2023
03/07/2023	SAN VICENTE	Vado Ligure	30	02/08/2023

I never worked with many-to-many measures, and I'm struggling to get the expected result,

There is a way to solve this question? Thank you!

Re: How to create measures for data with many-to-many relationship

gregoliveira — Sat, 15 Jul 2023 21:53:07 GMT

Hi.

I do not use many to many relationships as well, but if I need to use, my first shot will be read the SQLBI articles (https://www.sqlbi.com/topics/many-to-many/ and https://www.sqlbi.com/articles/different-options-to-model-many-to-many-relationships-in-power-bi-and-tabular/).
Hope one of them can help you.

Re: How to create measures for data with many-to-many relationship

Anonymous — Mon, 17 Jul 2023 02:03:17 GMT

Hi Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Then create a measure.

Measure = MAX(Table1[DATE])+MAX(Table2[TT])

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: How to create measures for data with many-to-many relationship in DAX Commands and Tips

How to create measures for data with many-to-many relationship

Re: How to create measures for data with many-to-many relationship

Re: How to create measures for data with many-to-many relationship