Row level security on two personas with single report

[Alatha]

Hi everyone, regarding RLS.
I have one report and it has two personas site owners ans service owners.
And created 2 roless
site owners - rls applied
Service owners - no filters
as I have single report and it has home page with 2 buttons visit site owner view & visit service owners view and based on the  page navigations
now scenario 1. - when site owners access the report should see site owners button and should able to see the sites they own.
scenario -2 - when service owner access the report should see both buttons and when clicks on service owners should see all sites and clicks on site owners should see only owned sites.
working as expected -Site owners access able to see only single button and also see the sites they own
problem - when service owner access the report and able to see both buttons and clicks on service owners button able to see all sites BUT when clicks on site owners button also seeing all sites instead of sites they own.
please assist me to achieve this as expected

[v-sshirivolu]

Hi @Alatha ,

I would also take a moment to thank @Jaywant-Thorat  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

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

[v-sshirivolu]

Hi @Alatha ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[Jaywant-Thorat · Accepted solution]

Hi @Alatha ,

You’re very close already, the issue you’re facing is a classic RLS + navigation + role-evaluation limitation in Power BI, not a mistake in your logic. Let me explain why it happens and then give you correct architectures to achieve your expected behavior.

Root Cause (Very Important):

RLS is evaluated only once per user session, not per page or button click.

So in your case:

• Service Owner is mapped to a role without filters
• Once the report loads, Power BI decides:
  • “This user has unrestricted access”
• Page navigation cannot re-apply RLS
• Therefore:
  • Service Owner >> Site Owner page >> still sees ALL sites

This is by design in Power BI. Buttons, bookmarks, and navigation cannot change RLS context.

What You Want:

Persona	Page	Expected Data
Site Owner	Any page	Only owned sites
Service Owner	Service Owner page	All sites
Service Owner	Site Owner page	Only owned sites

This means conditional data restriction inside the same role. RLS alone cannot do this.

Solution 1: Use Dynamic RLS + USERPRINCIPALNAME(), instead of “No filter” role for Service Owners.

Step 1: Create a Security Mapping Table named 'UserAccess'

UserEmail	SiteID	AccessType
a@org.com	S1	Site
a@org.com	S2	Site
a@org.com	ALL	Service
b@org.com	S3	Site

Step 2: Single RLS Role:

Apply this filter on UserAccess table:

---DAX---

UserAccess[UserEmail] = USERPRINCIPALNAME()

---DAX---

Now both personas are handled dynamically.

Step 3: Control Data by Page (KEY PART)

Create a Page Type table (Disconnected):

PageType
Site
Service

Step 4: Measure to Control Visibility

---DAX---
CanSeeSite =

VAR IsServiceOwner =

    CALCULATE(

        COUNTROWS(UserAccess),

        UserAccess[AccessType] = "Service"

    ) > 0

VAR PageTypeSelected =

    SELECTEDVALUE(PageType[PageType])

RETURN

SWITCH(

    TRUE(),

    PageTypeSelected = "Service", 1,

    PageTypeSelected = "Site" && IsServiceOwner, 1,

    PageTypeSelected = "Site" && NOT IsServiceOwner, 1,

    0

)

---DAX---

Step 5: Filter Your Visuals

Apply Visual-level filter:

CanSeeSite = 1

Result:

Service Owner >> Service page >> All sites

Service Owner >> Site page >> Only owned sites

Site Owner >> Only Site page >> Only owned sites

=================================================================

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach

LinkedIn: https://www.linkedin.com/in/jaywantthorat/

Join #MissionPowerBIBharat: https://tinyurl.com/JoinMissionPowerBIBharat

#MissionPowerBIBharat

LIVE with Jaywant Thorat

[DanieleUgoCopp]

Hello,
I think you can fix it by using one RLS role only and putting the logic inside the rule, create a mapping table with user email, persona, and site id.

Use this RLS rule:

IF ( LOOKUPVALUE( UserAccess[Persona], UserAccess[UserEmail], USERPRINCIPALNAME() ) = "ServiceOwner", TRUE(), Sites[SiteID] IN CALCULATETABLE( VALUES(UserAccess[SiteID]), UserAccess[UserEmail] = USERPRINCIPALNAME() ) )

Use the Persona value to control which buttons are visible on the home page so the service owners will see everything, and when they switch to the site owner view they’ll only see their own sites.

Best,
Daniele

[Alatha]

Thank you Daniele for your reply.
I applied RLS role as you suggested 
"" IF ( LOOKUPVALUE( UserTable[Persona], UserTable[Owners], USERPRINCIPALNAME() ) = "ServiceOwner", TRUE(), UserTable[SiteID] IN CALCULATETABLE( VALUES(SiteData[SiteID]), UserTable[Owners] = USERPRINCIPALNAME() ) ) ""

the result is showing rls filteredout the data in both site owners and service owners view BUT the expectation is serivce owners view should able to see all sites.
current model -
sitedatatable - contains all sites
usertable - siteid, owners, persona (persona is calculated column 
IF(
UserTable[Owners] = "abcuser@del.com",
"SiteOwner",
"ServiceOwner"
)
connected Usertable to sitedatetable on SiteID with both direction and apply security filters
ex - UserTable

SiteID	Owners	Persona
1	abcuser	ServiceOwner
2	abcuser	ServiceOwner
3	abcuser	ServiceOwner
4	xyzuser	SiteOwner
5	xyzuser	SiteOwner
6	xyzuser	SiteOwner

Please let me know if am missing anything