Custom dynamic RLS problem

[morz3d]

Hi all,

I have specific problem i wanted to solve with RLS

Brief description follows.
Problem:

• There are 2+ group of users devided into regions (EU, US,...)
• User from EU region should see data on daily granularity for his region while for other regions it should be able to see monlty aggregates only for completed months, and same alies for users from other regions

I tried to solve it like this

I prepare my fact table so it holds both daily and montly granularity while assigning specific security key for each record. Last 2 records collored in orange are actually just aggreagates for specific region for past month (EU: 100+200=300; US: 100+400=500)

region	date	amount	security_key
EU	2023.02.01	100	EU_daily
EU	2023.02.15	200	EU_daily
EU	2023.03.15	50	EU_daily
US	2023.02.01	100	US_daily
US	2023.02.15	400	US_daily
US	2023.03.15	10	US_daily
EU	2023.02.28	300	EU_monthly
US	2023.02.28	500	US_monthly

Where my security table is like following, and it gives minimum permission for sertain user/groups:

user	security_key
user_eu	EU_daily
user_eu	US_monthly
user_us	US_daily
user_us	EU_monthly

For end users this problem is solved and solution works as expected, but the problem is that for BI developers (my user) i always get wrong results (daily and montly are added up) as RLS is ignored for server/workspace admins. 

Any ideas how to solve this problem other way so accurate results are displayed for end users and admins as well?

[Anonymous]

Hi @morz3d ,

If you want to apply RLS, please confirm all the users is Viewer role in the workspace.

For more details, you can read related document: Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.