topic Re: How to join 2 table that has many-to-many relationship in DAX Commands and Tips

How to join 2 table that has many-to-many relationship

CKK — Sun, 11 Feb 2024 09:02:20 GMT

Hi all,

My question is below. I have two tables connected to each other with "Order" column and many-to many relationship. I want to get a new table shown as below. Is there any way to have it? I am a newbie so, if it is a dumb question, sorry about that.

Thanks in advance.

Re: How to join 2 table that has many-to-many relationship

talespin — Sun, 11 Feb 2024 09:57:41 GMT

Hi CKK

No need to be sorry, create three tables instead. Avoid Many to Many relationships.

Table1 unique parts

Table 2 unique operations

Table 3 Orders

Join Table1 and Table2 to Table3 using One-Many relationship.

You do not need to create a combined table, what is it you want to do with combined table?

Problems with combined table

- Additional storage.

- Slow refresh time.

Re: How to join 2 table that has many-to-many relationship

Jihwan_Kim — Sun, 11 Feb 2024 14:46:55 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

GENERATE function (DAX) - DAX | Microsoft Learn

Expected result table = GENERATE('Table 1', SUMMARIZE('Table 2', 'Table 2'[Operation]))

Re: How to join 2 table that has many-to-many relationship

Anonymous — Mon, 12 Feb 2024 10:09:20 GMT

Hi CKK

You can use Merge to achieve this effect.

Delete the Order column

Is this the result you expect?

For more details, you can read related document link: Merge queries overview - Power Query | Microsoft Learn

Best Regards,
Yulia Xu

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