Many to many issues and alternatives in Power BI

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

[@antique-bs8bb]

Very good. Thanks. I think of a Bridge table as a master list that allows me to filter either table. I'm just wondering if you could merge 2 bridge tables, one created from Budget and one from Actuals to be 100% certain you catch all unique Fruits in both tables. In other words find a way to include Mango in the Bridge Table. And stacked tables seem a useful tool.

[@learnspreadsheets]

Thanks for the comment! Actually I had a video segment about appending the two bridge tables but the video was too long so I cut it from the final version but that is such a great suggestion!

[@vishalmane3139]

hw to join two tables if they dont have any common column between them ?

[@learnspreadsheets]

Hi I’m not sure how you could join them if there is no common column, it would require the app to guess which ones match. You need to find a way to have a common column even if it means creating one manually or via power query

[@alishkumarmanvar7163]

Hello Brother, you have told in video saying that have created another video where is it. please share link

[@learnspreadsheets]

I mention dates in Power BI, the link is in the video description, but here it is again for you: kzbin.info/www/bejne/epuXeoWbZdaNhtksi=19SbyoMMez5W1Iks

[@danjarupath]

Cool

[@learnspreadsheets]

Thanks!

[@user-xd1nz6of2n]

I used the same workaround as Bridge table that you have mentioned. But when I pull in the fields of all three tables into 1 visual, it is giving me an error "Can't determine relationship between the fields". Can you please let me know how to handle this?

[@learnspreadsheets]

Thanks for the question. Unfortunately it’s a different reason every time so it’s hard to answer. I’d recommend posting in a forum where you can add screenshots & even upload the file that way others can help