How to show different data linked by a middle man list

[IDLiveTim]

Hello PowerBI Community, 

I am currently creating a Risk management solution, and have a list of Risks in Sharepoint with several different fields. 

I then have a list of Controls with different fields, which link to a risk using a middle man list with the ID of Control and ID of Risk. This allows a many to many sort of relationship using a sharepoint list. 

In my dashboard, I want a user to be able to drillthrough to a control from a table showing them all, to a 'Control on a page' page, where there will be another table with all the associated risks for that particular control listed (Checks the Middle Man list for that particular ID then pull through any risks that have that link). I am not sure how to achieve this, whether using a calculated column or otherwise. 

If anyone could help point me in the right direction it would be greatly appreciated. 

Screenshots below:

Risks List

Control List

Middle Man List

Thanks again

[v-bofeng-msft · Accepted solution]

Hi @IDLiveTim ,

Would you like to establish a many-to-many relationship between the 'Controls' and 'Risks' tables by using 'RiskControls' as a bridge table, so that users can drill down from the control to the corresponding risk in the BI report?

If that’s the case, you simply need to establish relationships between the 'Control' table and the 'RiskControls' table, as well as between the 'Risks' table and the 'RiskControls' table in the model view. I’ve made a test for your reference:

1\My lists

2\Model view

3\My report

Best Regards,

Bof