Filtering the 1 side of the relationship ("many-to-1") in PowerBI datamodel

[treatas]

Hi everyone,

I am working on setting up RLS on the territory table of the dataset I am working on. My RLS table contains all the possible combinations of UserEmail and authorized TerritoryID.

I want to link RLS table so that the P0_Id filter flows into the Territory table, but end up with a many-to-one relationship. Here are the different approaches I tried so far:

Directly linking the RLS to Territory

1 staging table

2 staging tables

I feel like I am close to getting my RLS table to flow into the Territory table, but I can't get my head around how to contournate the directional filtering issue.

TLDR: Has anyone had experience with making the many side of a "many-to-1" relationship filter the single side of the relationship ? either using helper tables, or with another approach ? especially in the context of setting RLS at datamodel level

Thanks for reading!

[lbendlin]

That's a rather unfortunate situation. Don't bother with the bridge tables.  Consider splitting the RLS table into two. At a minimum make sure that the security flows both ways too.