Many to many (M2M) relationships in Power BI should be used with caution, there are safer alternatives such as creating a bridged or stacked table that I showcase in this video. Email me for the file to analyse.

Example files can be found here: www.xlconsulting-asia.com/youtube-files

In general. Many to many can cause ambiguity between tables. In particular, results wont be what you expect if 1) Two or more fields/dimensions are common amongst both related tables (including the field with the relationship), this will lead to repeated numbers which are incorrect 2) There are not the same values in both, e.g. Mango is in fruit column for one table but not the other. This will lead to totals not adding up when DAX is used.

Alternative 1: Create a bridge table relating both, unique values (sometimes just one column) that acts as the one side of both tables, there needs to be one per common field though. I show how to make one in Power Query.

Alternative 2: Stack tables on top of each other in Power Query. There are few columns and many rows, it could even be one "Values" column and one "metric" column associated so for example gross profit and expenses and #Customers could be stacked but then they are split through DAX measures. This works well with multiple overlapping fields/dimensions, even if some fields are in one table but not others it still works well, but there will be blanks in certain cases.

Power BI date tables:    • Dates in Power BI: All set up steps y...  

Chapters
00:00 - Introduction
01:30 - Many to many ambiguity
06:11 - Stacked table alternative
08:00 - Bridge tables theory
09:39 - Create stacked table
15:00 - More complex stacking
16:35 - Bridge tables
18:55 - Which method when