How to Manage Many-to-Many Relationships in Power BI with Slow Changing Dimension Table

[tmhalila]

Hello Power BI Community,

I am facing a challenge with my Power BI data model.

I have two fact tables tracking indicators' performance daily and two dimension tables (dim_date and dim_site). The dim_site table includes information about sites supported by specific donors and sub-partners, with these assignments changing annually.

In my fact tables, each record includes attributes such as site name, site code, location (Region and Council), and the donor and sub-partner supporting the site during that specific period.

The challenge:
I want to use the dim_site table to create slicers for site-related fields (e.g., site name, location, donor, and sub-partner). These slicers should dynamically control both fact tables. However, when I attempt to create relationships between dim_site and the fact tables, it results in a many-to-many relationship. This is because the fact tables contain multiple records per site for each submission, while dim_site tracks multiple assignments of sites to partners (e.g., one partner in one year and another partner in the next).

📁Here is the sample (Example) of the power BI and data source CSV dataset.

[tmhalila · Accepted solution]

Hello team,

I have resolved the above problem using the model designed below. Data modeling requires extensive thought, but it addresses many scenarios.

NOTE, the relationship between dim_annual_site_subpartner_assignment and dim_site is bidirectional.

[lbendlin]

use measures as visual filters. Sense the selections from the dim_facility table slicers, and then return 1 or 0 if a row in the fact table should be shown or not. Add that measure to the "Filters on this visual"  and set its filter to "is 1"

You will have to figure out what to do when your dim_date interval and the dim_facility timestamps overlap. Use the latest timestamp, for example.

[tmhalila]

Thanks for your response @lbendlin . Since the real dashboard has a  lot of visuals, bookmarks and other complex measures already. I prefer if we can find a solution on model design.