Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using relationships vs merge join difference

hi, 

i have a question. When to use the relationships ( one to many, one to one , many t omany etccc) and when to use merge join(inner, outer) ?  whn we can join two tables using merge join without any relationships between tables, what is the use of relationships?

and sometimes there are lot of issues creating relationship between tables like null values, duplicate values,.etc.. what happanes if dont build relationships and just use joins? and vice versa? can somebody throw some light plz?  

6 REPLIES 6
Somasekhar_IND
New Member

When you load the data to Power BI, only a single table (MergeQueries) that combines the two tables is loaded to the data model. The original tables won’t be loaded into the data model as you will be disabling the load, hence less memory consumption and better performance.

When you compare the two PBIX files with the same imported data, you will see that the Relationship file is larger in size than the Merge file.

However, its depends on the scenario why you want to use Relationship or Merge in Power BI, like using some DAX functions for analysis that may not work when using Merge. Also, if you have a complex model, it is recommended to do as many modelling at the Datasource level. The less complex the model is, the better the performance in Power BI.

 

Refer the below link for deatailed explanation 

MERGE VS RELATIONSHIP IN POWER BI - Ruki's Blog (rukiatu.com)

 

Jesusmari
Frequent Visitor

Buenas,

 

 ¿hay mucha diferencia de rendimiento entre tener dos tablas unidas medienta un merge o un join? la percepción que yo tengo es que el merge es mas costoso, no obstante me gustaría saber vuestra opinión.

 

Saludos

 

Saludos

Anonymous
Not applicable

Hi, 

 

When working with a couple of huge files (SQL-DirectQuery) I got the error that there was too little memory to show the matrix when choosing 'Show items with no data' for the linked tables. 
When I didn't choose 'show items with no data' the relationship only showed values from the first table with related values in the second and third tables (practically making it an inner join relationship).
Using a left outer join when merging queries solved it.

Don't know if there is a fundamental advantage in performance when merging, compared to managing your relationships.
I guess there is more choice when you merge, because you can choose:
Left Outer, Right Outer, Full Outer, Inner, Left Anti, Right Anti 
and when you manage relationships, you can only choose
many to one, many to many, one to one and one to many.

Grtz

jvaught
Advocate I
Advocate I

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.

danavannz
New Member

hi reddy_powerbi, i have the same question!  Hopefully someone can answer it here, if I found out i will let you know too 🙂

v-shex-msft
Community Support
Community Support

HI @Anonymous,

 

When tables contains available relationships, you can direct table fields to build visuals and graphs, records which used in visuals will auto analysis and summarize on current category through existed relationship.

 

If not exists availed relationship, you can't direct use amount field from different tables, they not summarize so you need to write measure with particular filter conditions manually to display correspond summary result.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.