Forum Discussion
How to related tables with multiple fields in common?
Community ChampionHello udedomenico
in Power BI you have 2 possibility to handle this. Or you create a common key by combining your columns (as you already stated). I don't know why this should be a problem however (why should fields be removed?). The other possiblity is to merge them in Power Query and only extract the data you need. But in a scenario where you have a 1-n relationship, this would lead in duplicating rows.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello Jimmy801,
Many thanks for your reply and sorry if I delayed few days to come back to you, but it has been quite ecthic these days here....
First of all let me answer your question: why should fields be removed?
Look at the following example:
In the two tables Budget and Sales have 3 common keys: Year, Month, Customer Number.
If you create a composite key with the three mentioned keys then you must remove in one of the two tables the critical keys, otherwise the relationship between the two tables will still be based on multiple keys (actually one more key now, the composite one....) .
In the video i posted in my first post at 5'19" the technician does mention that the keys must be kept in one of the two tables only.
The second solution you propose (merging table) is certainly an option but as you said it can be a problem if we are merging facts tables or any large tables in general, because then we will have a huge, really huge big table as a result...At this point...I wonder how PowerBI handles memory.... does it load all tables in the data model in RAM when we are loading the workspace?
If that is the case then PowerBI is a limited product and certainly cannot be use for big data (or even large data actually)... unless we have other alternative other than creating compound keys or merging tables.
100 milion records tables (not a lot nowadays) in such scenarios can create problems to PowerBI.
I hope there are alternatives to the two mentioned solutions, compound keys and merging tables...
Umberto
- Jimmy801
Hello udedomenico
you were stating about removing fields in your post, and I asked whats that does mean 🙂
But in your scenario, why you are not connecting the the sales and budget table to the customer.customer number field? This should work be fine.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHello Jimmy801 ,
the reason why I need to connect sales and budget tables is because I would like to explore which customers had a budget and ended up buying or viceversa, they bought something but they never had budget.
I hope that explains.
Thanks,
Umberto
- Jimmy801
Hello udedomenico
but they are connected through the customer table. I don't see the reason to connect them in another way.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy