Forum Discussion
Using relationships vs merge join difference
I have the same question and maybe some additional clarity.
One reason I have found for using a relationship rather than a join is when the upstream table has information you need in more than one downstream table. If you use a merge, you have to do the merge multiple times, which also duplicates the data in the report. It also creates confusion when you have the same field showing up in both downstream merged tables in your report. You will get errors saying Power BI can't determine the relationship between two fields (quite frustrating when you know they came form the same source - the common upstream table that was merged into two downstream tables). In this case it's better to bring the common upstream table into the report and relate it to both downstream tables via relationships. The data is not duplicated and the relationship is clear.
But what about when you have separate dimension and fact table which only relate to each other and no other tables? Separating dimension and fact tables is good database design, but in my report it means I have to constantly select fields from both tables. Merging would make this way easier, but am I breaking some fundamental rule of good design when I do that?
Also, what are the performance considerations? Again, these questions only really apply to a situation where you have only one pair of tables in mind. As I stated above, when you have a table paired (whether by merge or relationship) to another table, you are better off with the relationship, as far as I know.