Data modeling: flip relationship direction

[TheoAu]

Hi,
With a large dataset, I encountered some relationship issues. Indeed, some tables do not filter the main fact table: project. The main goal of my dashboard is to show project information. The user should be able to filter these projects by using filters on the DIM table.

Let's take a look at a sample that corresponds to my main issue:
- Project is my main table I want to use in visuals.
- Product is mainly a table used in slicers.
- To avoid a many-to-many relationship between these two tables, I have a bridge table between product and project.

However, as the directions are Product -> BRD Product and Project -> BRD Product, the filters from Product do not propagate to Project.

Sample link: https://drive.google.com/file/d/1TsJ6mRvFgFoaT7u8aot3bihLP9q4JF4V/view?usp=sharing

I thought of three solutions:
- The quickest method could be to make Project->BRD product a double-sided relationship. However, if I do that in my large dataset, I could lose performance. Right?

- I could force a specific relation with a DAX crossfilter; however, this would mean that I must create a measure for every Project attribute I want to use to change the direction with BRD Product. I do not think this is the most valuable solution.

- The other solution could be to flip the direction to have BRD Product->Project instead of Project->BRD Product. However, Power BI does not allow me to do that. The tables get back to their original direction when I make a change. Both "update or delete relationship while actualizing data" and "Detect automatically new relations when data are loaded" can be checked or unchecked; this does not change the problem.

Do you have an hint?

Thank you !

[alish_b]

Hey @TheoAu ,

You are correct that switching to bi-directional filtering would be the easiest to solve this but it will not be a right choice in the long run due to performance concerns as well as unpredictable measure results.
Second choice will be too labor-intesive and third one is simply not allowed.
I had a similar requirement in the past and I actually pushed back on the requirement highlighting the technical challenges and how it would violate data modeling best practices and if possible, please try to do the same. It creates more problems than it solves.
Hope it helps!

[TheoAu]

Hi, thank you for your answer.
It's complicated to deny the client's suggestions because these filters are the whole purpose of this dashboard: showing specific information corresponding to the user's slicer selection. Also, the product table is not the only table that has the same issue (I have some other tables with the same architecture: a bridge table connected to the project fact table). However, it was easier to select only one table for this post sample.

[v-saisrao-msft · Accepted solution]

Hi @TheoAu,

I understand your situation. Since the client requires slicers to drive the dashboard, enabling bi-directional filtering on the bridge relationship is the most practical way to achieve this, even though it isn’t ideal for performance or model simplicity. A good compromise is to apply it selectively only on the specific relationships needed for the slicers rather than across the entire model. This way you get the required functionality while minimizing potential risks with ambiguity or slowdowns.

Thank you.

[TheoAu]

Hi @v-saisrao-msft thank you for your answer, I also think this is the most valuable solution. 

Have a nice day

[v-saisrao-msft]

Hi @TheoAu,

I understand your situation. Since the client requires slicers to drive the dashboard, enabling bi-directional filtering on the bridge relationship is the most practical way to achieve this, even though it isn’t ideal for performance or model simplicity. A good compromise is to apply it selectively only on the specific relationships needed for the slicers rather than across the entire model. This way you get the required functionality while minimizing potential risks with ambiguity or slowdowns.

Thank you.

[v-saisrao-msft]

Hi @TheoAu,

I have updated the direction in both places, and the slicer is now working. The PBIX file is attached for your reference.

Hope this helps

Thank you.

[TheoAu]

Hello, thank you for your answer.

Is there any other means to make the slicer working without using both-sided relationship?