Dynamic RLS

[osaz_nero]

Hi,

I have a rule to grant access based on hierarchy to Directors and Assistant directors to access rows where the person logged in matches the name of the person in either the Director's Email column or Assistant Director Email colum. And also grant special access to all report on the dash to specific users within the semantic model in the service, i have set up the row level security using the following code:

VAR CurrentUserEmail = LOWER(USERPRINCIPALNAME())

RETURN

    LOWER('Combined_Person_Post'[Director]) = CurrentUserEmail

    || LOWER('Combined_Person_Post'[Assistant Director]) = CurrentUserEmail

 

[osaz_nero]

Thanks all but the issue is that the director and assistant director are not able to see the report on the dashboard. As I wanted this to be dynamic, I wonder if I have to assign emails to the role created?

[v-tejrama]

Hi @osaz_nero ,

Dynamic RLS does not require you to manually enter each Director or Assistant Director email in the DAX expression. The USERPRINCIPALNAME() function evaluates the current user and compares it to the values in your Director and Assistant Director columns.

However, users still need to be assigned to the RLS role in the Power BI Service. Dynamic RLS determines which rows are visible, but does not automatically grant access to the semantic model or report. If Directors or Assistant Directors cannot view the report, first confirm they are added to the role under Dataset Security and have access to the app, workspace, or report.

Additionally, ensure the email values in the Director and Assistant Director columns match exactly with the USERPRINCIPALNAME() value for each user. Differences in UPN format, aliases, or blank entries may prevent rows from displaying. You can use the View As feature in Power BI Desktop or Service to verify the RLS filter is working as expected for each user.

Once role assignments and email values are correct, dynamic RLS should function properly without the need to maintain a separate list of Director or Assistant Director emails in your DAX code.

Thank you.

[v-tejrama]

Hi @osaz_nero ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[osaz_nero]

Thanks @v-tejrama ,

I was off work Thursday/Friday. Thanks for your feedback which is really helpful. My thought was that with the dynamic RLS, I won't need to assign users to the RLS role again. My goal was to eliminate the maunal adding of users to the RLS role or updating a group of users assigned to the RLS.

Thanks so much

[v-tejrama · Accepted solution]

Hi @osaz_nero ,

You are correct that Dynamic RLS eliminates the need to hardcode or maintain user filters within DAX. However, users still need to be granted access to the appropriate role. Dynamic RLS controls data visibility after authorization but Power BI requires users to be assigned to an RLS role either directly or via an Entra ID security group.

To streamline user management it is best to assign a security group to the RLS role and manage group membership as needed. This way Dynamic RLS will automatically filter data for each user based on their email without requiring DAX updates when Directors or Assistant Directors change.

Thank you.

[v-tejrama]

Hi @osaz_nero ,

Your current RLS logic restricts access to rows where the logged in user's email matches either the Director or Assistant Director column. If you need to grant certain users access to all data it is advisable to maintain those users in a dedicated security table and reference their membership in the RLS expression. This approach simplifies ongoing maintenance as privileged users can be added or removed without modifying the DAX code.

While a hardcoded list of email addresses may work for a small group, it is less scalable. Additionally, DAX string comparisons are generally case insensitive, so the use of lower is not necessary unless normalization is specifically required. If privileged users should always have unrestricted access, you may also consider assigning them to a separate RLS role without row restrictions.

The most suitable method will depend on how often the privileged user list changes and your preferred security management strategy.

Thank you.

[GilbertQ]

Hi @osaz_nero 

One thing I would mention as to why are you using the lower. As far as I am aware there is no case sensitivity within the DAX engine.

[oussamahaimoud]

Hi @osaz_nero,

Hope you're doing well!

Regarding your screenshoot, I can say your logic is OK. Which means Directors and Assistant Directors will only see rows where their email matches either column. That part works as expected.

For the special access, you just need to extend your RETURN block with an OR condition that checks against a hardcoded list of those privileged emails:

VAR CurrentUserEmail = LOWER(USERPRINCIPALNAME())

RETURN

    LOWER('Combined_Person_Post'[Director]) = CurrentUserEmail

        || LOWER('Combined_Person_Post'[Assistant Director]) = CurrentUserEmail

        || CurrentUserEmail IN {

            "user1@yourdomain.com",

            "user2@yourdomain.com",

            "user3@yourdomain.com"

        }

In order to have a solid RLS :

• Make sure the emails in the IN {} list are lowercase, since you're already normalising USERPRINCIPALNAME() with LOWER(), a case mismatch will silently break access.
• If the privileged users are also Directors or Assistant Directors in the data, the rule already covers them, but adding them to the whitelist explicitly doesn't hurt.
• After saving, test in Power BI Service for both a standard Director and one of the whitelisted users to confirm each sees the right scope.

Assisted by AI for clarty of word!

Hope this helps! Don't forget to accept as solution and like it in order to keep helping others.

Best regards,

Oussama (Data Consultant - Expert Fabric & Power BI)

[osaz_nero]

Thanks but the issue is that the director and assistant director are not able to see the rreport on the dashboard. As I wanted this to be dynamic, I wonder if I have to assign emails to the role created?

[SaiTejaTalasila]

Hi @osaz_nero ,

The rule you shared looks correct for restricting access based on Director and Assistant Director emails. To also allow special users full visibility, you can extend the condition with an additional or clause that checks against a list or table of privileged emails. This way, hierarchy-based filtering is preserved while exceptions are handled cleanly. Using a separate table for special users makes it easier to maintain without editing the DAX each time.

Thanks,

Sai Teja