Data structure - Many to many relationship requirement?

[metcala]

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

[mlsx4 · Accepted 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

[metcala]

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!