Forum Discussion
How to related tables with multiple fields in common?
Hello all, I am working to get familiar with advanced PowerBI scenarios and I see a potential problem which I would like to share with you, maybe you know how to deal with similar scenarios.
PowerBI working model is to relate tables by using one key field for each table.
However sometimes it is necessary to create relationships based on multiple fields.
I understand that one way to resolve such a situation is to create compound keys, as greatly explained in this video:
https://www.youtube.com/watch?v=sVACTQh5bOg&feature=youtu.be
This works OK, but sometimes this approach could prevent visibility of data from one table to another, since in one of the tables the original fields are to be removed.
My question is, how do we deal with similar scenarios? Merging tables? But could that not affect memory usage, since the resulting table becomes huge, having many duplications?
Thanks to anyone who has the knowledge, the time and kindness for replying to me.
Umberto
7 Replies
Jimmy801Community Champion
Hello 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
JimmyHello 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
Jimmy
