Forum Discussion
Data structure - Many to many relationship requirement?
Hi all
Just after some advice for best practice establishing relationships in a PowerBI model.
I have a 'Open Cases' table (see below) which is refreshed monthly. I also have a 'Narratives' table (see below) with a many to many relationship as there can be multiple updates on a particular case and if the case is open for multiple months it will appear multiple times on the 'Open Cases' table.
Open Cases
| Reporting Month | Case ID | Category | Assigned to | Expected Close |
| ... | ... | ... | ... | ... |
Narratives
| ID | Update Date | Updated by | Narrative |
| ... | ... | ... | ... |
This does give me what I need but just want to explore whether there would be a better way to build this relationship.
Thanks
Depending on the size of both tables, you may consider the option to create a "Bridge table". Otherwise, you might miss pieces of data that, later on, could impact on your results. You can check this article: https://www.seerinteractive.com/insights/join-many-many-power-bi
3 Replies
mlsx4Memorable Member
Depending on the size of both tables, you may consider the option to create a "Bridge table". Otherwise, you might miss pieces of data that, later on, could impact on your results. You can check this article: https://www.seerinteractive.com/insights/join-many-many-power-bi
Thanks for the response.
Just been having a look into bridge tables and looks like an good option. I'm just struggling to rationalise it in my head am i correct in thinking it would be something like this...
Open Cases
Reporting Month Case ID Category Assigned to Expected Close 1/1/23 123 A JS 31/12/23 1/1/23 124 A PJ 31/10/23 1/2/23 123 A JS 31/12/23 1/2/23 125 B SP 31/12/23 Narrative
ID Update Date Updated by Narrative 123 5/1/23 AK Test underway 123 28/1/23 AK Test completed 124 29/1/23 JM Investigation brief 123 31/1/23 AK Assessment write up Bridge
ID 123
124
125
Hopefully my understanding is correct.... is it in effect checking for unique values in each table listing in the bridge table and allowing a many-to-one relationship from Open Cases to Bridge and Narratives to Bridge?
- mlsx4
Yes, you have done it right. Should work for checking the values!