When to use MERGE or RELATIONSHIP in Power BI // Beginners Guide to Power BI in 2020

[@ExcelWithChris]

Thanks. Exactly what I was looking for. I have used both for a while and suddenly realised it is sort of the same thing. But wanted to know why the one rather than the other. Thanks.

[@DawoodChoudry-r3j]

but in this case we can have lot of NULL values in fact table if we make left join, or we use inner join we can also lost data in this way as well. so whats the best solution for it? Thanks!

[@kirillperian]

In Power BI the power of performance is in the star schema. If you flatten the dimensions (as you mentioned in the video) into facts you lose out on the performance, as well as complexity of DAX code you will have to write. What you'd want to do is flatten the dimensional hierarchy (if one exists) into dimension tables that are only one degree of separation from the fact table(s).

[@cdcolquitt]

That’s makes more sense to me

[@EyiBillion]

Love your insight.

[@JP-nn8tc]

haha do YOU have any videos?? I'm thinking your logic is where I need to go with my dataset. I have 4 enormous PBI data sets pulled in to one new PB model. They have identical columns and names. If they were smaller I would just Append them - and that would get me the resulting model that I need. But they're too big to do that. Do I have to create a fact table in the consolidating PB model?

[@rahulgautam511]

Thanks for the video. Which is more performance efficient Joins or Relationships? if we have big data

[@SolutionsAbroad]

Hi there, relationships tend to be more efficient than joins for bigger datasets, especially when you're joining up fact tables with millions of rows. But if you're working with tiny datasets, its simpler to do joins

[@kiranmai8773]

@@SolutionsAbroad can you please explain why relationship are better for bigger tables and why joins are better for small data sets?

[@wungJuni]

@@kiranmai8773 he actually explained already...haha... if you watch the video attentively.

[@qayyumkadri2138]

If we don't have a unique column in both the tables Fact & dimension. Then what approach to follow? create a bridge table, merge or many to many?

[@SolutionsAbroad]

Hi, if there is no unique column on both sides I typically create a bridging table. I always avoid many to many relationships whenever possible because it can be pretty unpredictable

[@kingthranduil8807]

Is it necessary for 2 tables to be related before I can do a merge?

[@SolutionsAbroad]

Not at all, as long as they have a column they can merge by, that should be all you need

[@kingthranduil8807]

@@SolutionsAbroad thanks! More power to your channel, bro!

[@vinhhung23]

Very clear instruction. Thank you. Just a quick question. When we use merge, we can match multiple criterias in main table with dimention table, while we can not do the same with relationship right ?

[@veeek8]

yes thats correct

[@erinfield1943]

Actually it should be MERGE or RELALATIONSHIP or RELATEDTABLE, right?

[@vishalmane3139]

merge or RT are sa,e

[@EyiBillion]

Brilliant! Bye bye to Vlook-up.

[@veeek8]

index and match if you have to vlookup has flaws

[@cdcolquitt]

So you basically created a flat file? I thought it was a golden rule to NOT do that

[@SolutionsAbroad]

Hi there, thanks for watching! Working with flat files can't be avoided sometimes, either limitations to the systems or process, so the general rule of thumb is to work with relational structures (like the star schema I show in this video) If you mean creating a one, single flat table through merging, this can be a valid way to of approach too in certain cases, like simplifying your schemas for example. Hope that helped

[@cdcolquitt]

@@SolutionsAbroad I guess I meant you took multiple files and through merging, created what would look like a flat file. At what point point does the processing time for the queries and the calculations become resource intensive? I’m thinking from a power pivot/query point of view in excel as well.

[@primaconsulting9919]

👍

[@riazuddin7493]

I had sent you an email, can you kindly reply me. I would appreciate Fernan.