Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
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?
Yes, you have done it right. Should work for checking the values!