RLS is not working with Many to one relationship when used with Userprincipalname()

[ck1995]

Dear Community,

I have created a dashboard , which contains data arranged from various files that have a relationship with my Region master table.

Branch	Region
Mumbai	West
Delhi	North

And My access list has columns , as shown

Branch	User
Mumbai	Branchmanager@email.com
Delhi	Branchmanager@email.com
Mumbai	Regionmanager@email.com
Delhi	Regionmanager@email.com

I have created a relationship between Branch from access list and Branch from region master ,with Many - to - one cardinality and both direction filter . And i have created two roles that says user = userprincipalname() and in Dataset security i have given access to region managers and branch managers  accordingly. But it has not worked , so I had to create a one to one relationship between branch in access list and region master and increased columns according to Designation. But in documentation i read Many- to - one relationship works.

Branch	Branch manager	Regional Manager
Mumbai	BranchmanagerMumbai@email.com	regionalmanagerWest@email.com
Pune	BranchmanagerPune@email.com	regionalmanagerWest@email.com
Delhi	Branchmanagerdelhi@email.com	regionalmanagernorth@email.com
Noida	BranchmanagerNoida@email.com	regionalmanagernorth@email.com

Is there a way i can make many-to-one relationship as the designation columns are soon going to end and there might be a requirement to have multiple managers in same branch down the line.

[v-kkf-msft · Accepted solution]

Hi @ck1995 ,

By default, row-level security filtering uses single-directional filters, regardless of whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filter with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox.

For more information，you can refer to：How does Row Level Security works when there is a BI-Directional Filter in Power BI/Tabular Model? 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quick

[srkase]

May be the following suggestion will work out..

Based on region level, you can first create the role name  and then in the region master table you can define the code.

In my case,

I have defined the roles based on area wise / business wise.

Once you have published the report on BI online platform, you can go to DATASETS > Security and you can assign the role to the licensed BI user name.

I have applied the same for my dashboard system and it works fine..

May be this can help you..

[ck1995]

Thank you for the prompt response. As of now I am creating the same , A new table that has a column "Branch" which can connect with my region master table "Branch" column with one - to - one cardinality and according to designation I am creating columns and based on designations i will create rows. I will perform a test and check and give you a feedback. I am not sure how the DAX in data filter will work as I am using "USERPRINCIPALNAME()" to provide access

[srkase]

Can u send me the file with limited data? I can modify the RLS under roles and send back to you

[ck1995]

the problem is , the dashboard is made up of 110 excel files , so it will be difficult to make changes. But if you can share what type of changes you were planning on making , I can try and give the feedback.  I have created the relationships with all files with my region master table , so branch is in relationship with all files with many-to one relationship