Dynamic RLS with All Country Access

[vin26]

Hello,

I need to create a dynamic role with country-level and all-country access (Admin Level). Please see the data format below. Please let me know if there is a quick solution.

User Table:

Counry Table:

[v-karpurapud]

Hi @vin26 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[vin26]

Hi @lbendlin Thank you for the response, could you please help to write Dynamic and Static role in same RLS Role?

[v-karpurapud · Accepted solution]

Hi @vin26 

To define a single RLS role that includes both dynamic and static logic. Consider the following steps:

1. Admin users to see all data.

2. Regular users to see only the countries assigned to them in the mapping table.

DAX

OR (

LOOKUPVALUE('UserAccessTable'[Role], 'UserAccessTable'[email_ID], USERPRINCIPALNAME()) = "Admin",

[Country] IN

SELECTCOLUMNS(

FILTER(

'UserAccessTable',

'UserAccessTable'[email_ID] = USERPRINCIPALNAME()

),

"Country", 'UserAccessTable'[Country]

)

)

This approach ensures everything is handled in one role, which works perfectly in Power BI Service.

If this post helps, kindly mark it as Accepted Solution.

Thank You.

[v-karpurapud]

Hi @vin26 

We would like to confirm that the solution provided by @lbendlin  is accurate and resolves the issue. Could you please confirm if your query has been resolved? If it has, kindly mark the helpful response and accept it as the solution. This will help other community members resolve similar issues more efficiently. If not, please provide detailed information so we can assist you better.

Thank you

[lbendlin]

For true Dynamic RLS you woud need to assign user3 to all countries individually in your mapping table.

But you can have a second, static role that returns true for all admin users, and that can then overrule the first role.