Why / when to use bridge tables with many to many relationships

[campelliann]

Lets say I have this table with users for dynamic row level security (here the key between tables is Department):
Dim_Table

User email	Department
johndoe@email.com	dep1
marydoe@email.com	dep1
john.doe@email.com	dep2

I have then a fact table with the revenue.
Fact_Table

day	Department	revenue
1jun/22	Dep1	1000 dolars
2jun/22	dep1	100 dolars
3jun/22	dep2	9999dolars

Nowadays powerbi allows for single direction filter in M2M relationships. Is it wrong to have  Dim * --------->* Fact?

Or would it be advisable to create a bridge table with the departments to avoid many to many, but which would imply a bidirectional filter. Like     Dim* <-------->1 Dim_Department 1----------*> Fact table.

And why one solution is better than the other, or anything goes :)? 

Many thanks

[Anonymous]

Hi  @campelliann ,

Refer from :

Row-level security (RLS) with Power BI

By default, row-level security filtering uses single-directional filters, whether the relationships are set to single direction or bi-directional.

You can manually enable bi-directional cross-filtering with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox.

Note that if a table takes part in multiple bi-directional relationships you can only select this option for one of those relationships. Select this option when you've also implemented dynamic row-level security at the server level, where row-level security is based on username or login ID.

For more information, see Bidirectional cross-filtering using DirectQuery in Power BI Desktop  and  Securing the Tabular BI Semantic Model .

Best Regards,
Rico Zhou

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

[campelliann]

Thank you for your reply. What about the bridge table? Would you connect the tables above with many to many? Or would you create a bridge table with each department (dim table), between to avoid many to many. And why. 

Many thanks again 🙂