data modeling

[misgirl_ny]

Hi,  i'm relatively new in Power BI. Having issue with implementing many to many relationships in data model. I have a fact table with VisitID and MedicationID combination as a primary key. I have a dimension table with 2 fields : DimID, Description. 

Dimension table contains descriptions of different combinations of  Medications, based on a pretty complecated business logic. 

There's another intermidiate table with combination of VisitID and DimID as primary key. Each Visit may have multiple Medications and multiple references  to Dim table. I want to use the DIM table as a slicer and be able  to 'slice and dice' my fact table data. Cant figure out how to build relationships . Any help is greatly appreciated!!

[v-henryk-mstf · Accepted solution]

Hi @misgirl_ny ,

To implement a many-to-many relationship in your data model in Power BI, you will need to use a bridge table. A bridge table is a table that contains the many-to-many relationship and links two other tables together.

- In your case, you can use the intermediate table with the combination of VisitID and DimID as the primary key as the bridge table. You can then create a relationship between the bridge table and the fact table on the VisitID column, and a relationship between the bridge table and the dimension table on the DimID column.

- To use the dimension table as a slicer and slice the fact table data, you will need to create a slicer visual in your Power BI report and select the dimension table as the slicer field. Then, you can use the slicer to filter the data in the fact table by selecting the desired values in the dimension table.

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[misgirl_ny]

Hello, Henry -

Thank you so much! Somehow I missed /wasn't notified about available solution until now. I've already figured it out exactly the way you suggested. Thank you again!