Dynamic Row level security

[raju8004212]

Hi @Everyone 

dynamic swithcing of rls is  based on slicer selection
rls logic working with out selection if we  include slicer selection  in rls its not wrking 

my model is like bu_org ,snap connected to combof want to switch rls based on selection
before selection 

WC_BU_ORG_DH[PU_VALUE] IN 
    CALCULATETABLE(
        VALUES(Union_table[ANCESTOR_KEY]),
        FILTER(
            Union_table,
            UPPER(Union_table[EMAIL_ADDRESS]) = UPPER(USERPRINCIPALNAME())
        )
    )

 rls logic on snap_d_:

WC_EMPLOYEE_snap_D[ANCESTOR_KEY] IN 
    CALCULATETABLE(
        VALUES(Union_table[ANCESTOR_KEY]),
        FILTER(
            Union_table,
            UPPER(Union_table[EMAIL_ADDRESS]) = UPPER(USERPRINCIPALNAME())
        )
    )

if use above  logics independently those are working now im making like 
created disconnected table with type hr and pu  and making my logics like

updated with slicer selection_bu_org_dh rls dynamic
IF (
SELECTEDVALUE(Union_table[Type]) = "PU",
WC_BU_ORG_DH[PU_VALUE] IN
CALCULATETABLE(
VALUES(Union_table[ANCESTOR_KEY]),
FILTER(
Union_table,
UPPER(Union_table[EMAIL_ADDRESS]) = UPPER(USERPRINCIPALNAME())
)
),TRUE()
)

updated rls for snap_d:

IF (
SELECTEDVALUE(Union_table[Type]) = "HR",
WC_EMPLOYEE_snap_D[ANCESTOR_KEY] IN
CALCULATETABLE(
VALUES(Union_table[ANCESTOR_KEY]),
FILTER(
Union_table,
UPPER(Union_table[EMAIL_ADDRESS]) = UPPER(USERPRINCIPALNAME())
)

),
TRUE()
)

now the updated logic needs to work like if we select pu the pu logic needs to apply rls else it shows all values like no filter vice versa for sanp_d table

please hep me to work this 

thanks in advance

[Mo_d2]

Research Assistance: Expert Survey on Visual Analytics Security and RLS Challenges (12 minutes)

Greetings to our valued Power BI experts,

I am [Mohamed Fawaz Ismail], and I am conducting a specialized study on "Visual Analytics Security" (Secure Visual Analytics). The study aims to identify the actual gaps and risks that developers and analysts face when dealing with sensitive data within dashboards (particularly in relation to RLS, encryption, and Access Control).

Your practical experience in designing and securing dashboards using Power BI is the foundation for the success of this study. Your opinion on questions related to:

Applying the Least Privilege Access principle.

Dealing with accidental data leaks.

The importance of auditing and monitoring (Logging & Auditing).

...It will help us formulate better security standards for the community.

Duration: only about 12 minutes.

Survey link: [https://docs.google.com/forms/d/e/1FAIpQLScUNJwYADW3zyv8HcX4Js8xsLClet4NyogKoYHwanJ4kbY0ww/viewform?...]

Please take a few minutes to participate. I would be happy to share a summary of the results with you later.

Thank you very much for your time and cooperation.

[Mohamed Fawaz Ismail]

[Shahid12523]

True RLS → Merge PU & HR into one mapping table and write one RLS filter based on USERPRINCIPALNAME().

Report-level workaround → Skip RLS, use a measure filter with slicer to simulate security.

For real security → use option 1. 

[v-sshirivolu]

Hi @raju8004212 ,
Thank you for reaching out to Microsoft Fabric Community Forum.

Union_table: stores role-to-user mapping
EMAIL_ADDRESS | TYPE | ANCESTOR_KEY
user1@domain.com | PU | 1001
user1@domain.com | HR | 3001
user2@domain.com | PU | 2002

Create Single Role with Conditional RLS Logic - Dynamic_RLS 

Apply Filter to WC_BU_ORG_DH
WC_BU_ORG_DH[PU_VALUE] IN
SELECTCOLUMNS(
FILTER(
Union_table,
UPPER(Union_table[EMAIL_ADDRESS]) = UPPER(USERPRINCIPALNAME())
&& Union_table[TYPE] = "PU"
),
"Key", Union_table[ANCESTOR_KEY]
)

Apply Filter to WC_EMPLOYEE_snap_D

WC_EMPLOYEE_snap_D[ANCESTOR_KEY] IN
SELECTCOLUMNS(
FILTER(
Union_table,
UPPER(Union_table[EMAIL_ADDRESS]) = UPPER(USERPRINCIPALNAME())
&& Union_table[TYPE] = "HR"
),
"Key", Union_table[ANCESTOR_KEY]
)

Test the RLS

View as Roles - Select "Other User" and enter an email address such as user1@domain.com. Test:

In WC_BU_ORG_DH, you should only see rows where PU_VALUE is 1001.

In WC_EMPLOYEE_snap_D, only rows with ANCESTOR_KEY equal to 3001 should be visible.

Please find the attached .pbix file for your reference

Regards,

Sreeteja.

[raju8004212]

Hi @v-sshirivolu 

sreeteja thanks for your response , i need to select which rls data i want to see using toggle mechanism is it possible to pass variable to rls filter

[v-sshirivolu]

Hi @raju8004212 ,
Try These Steps - 
Employee Table
Employee_Name | PU_ID | HR_ID
John | 3001 | 1001
Steve | 1001 | 2002

User Table
Email | UserID
user1@domain.com | 1001

Create Disconnected Table
User_Role =
DATATABLE(
"TYPE", STRING,
{
{"HR"},
{"PU"}
}
)

Add Slicer to Report
Add User_Role[TYPE] as a slicer for role toggle

Apply RLS on Employee Table

VAR selectedRole = SELECTEDVALUE('User_Role'[TYPE])

RETURN
SWITCH(
TRUE(),
selectedRole = "HR", Employee[HR_ID] = LOOKUPVALUE(User[UserID], User[Email], USERPRINCIPALNAME()),
selectedRole = "PU", Employee[PU_ID] = LOOKUPVALUE(User[UserID], User[Email], USERPRINCIPALNAME()),
TRUE() // fallback - show nothing or all
) 
-----  This RLS rule changes based on the slicer selection.

Enable "View As Roles" for Testing

Navigate to Modeling > Manage Roles.

Apply the RLS to the Employee table using the provided expression.

Select View As Roles and choose user1@domain.com.

Now test by selecting HR or PU from the slicer.
You’ll see results change based on selected role

Please find the attached .pbix file for your reference.

Regards,
Sreeteja.

[raju8004212]

Hi @v-sshirivolu 

We are using pu from table a and hr from table b ,it's not passing parameters to rls

For your attachment case ur using same table to filter that will work because it's directly pass from filter, thanks for your response added few details in my one of the replies like data structure model relationship screenshot.

[v-sshirivolu]

Hi @raju8004212 ,
In your situation, the PU and HR IDs are stored in separate tables, so the slicer can't directly pass its value to RLS as it does in my example, where both IDs are in the same table. Therefore, you'll need an additional step such as creating a bridge table or using TREATAS—to link the slicer selection to both tables.

In my example, both columns were in the same table, so the slicer filtered directly without any extra setup.

To achieve this in your scenario, you have two options:

Option 1 – Bridge Table

1.Create a union table (using Power Query or DAX) that includes both PU and HR IDs, plus a column for the type.
RoleIDs =
UNION(
SELECTCOLUMNS(TableA, "RoleID", TableA[PU_ID], "RoleType", "PU"),
SELECTCOLUMNS(TableB, "RoleID", TableB[HR_ID], "RoleType", "HR")
)

2.Link this bridge table to TableA and TableB using their respective ID columns, adjusting relationships as needed.

3.Set your slicer based on RoleType and RoleID from the bridge table, and use TREATAS in the RLS filter to apply the slicer value to the target table:
VAR selectedRole = SELECTEDVALUE(RoleIDs[RoleType])
VAR selectedID = SELECTEDVALUE(RoleIDs[RoleID])
RETURN
SWITCH(
TRUE(),
selectedRole = "HR", TableB[HR_ID] = selectedID,
selectedRole = "PU", TableA[PU_ID] = selectedID,
FALSE
)

 

Option 2 – Disconnected Slicer with TREATAS
If you prefer not to change the model structure:

1. Leave the User_Role slicer disconnected.

2. In your RLS rule, use TREATAS to map the slicer value to the right table:
VAR selectedRole = SELECTEDVALUE(User_Role[TYPE])
VAR currentUser = LOOKUPVALUE(User[UserID], User[Email], USERPRINCIPALNAME())

RETURN
SWITCH(
TRUE(),
selectedRole = "HR",
CALCULATE(
COUNTROWS(TableB),
TREATAS({currentUser}, TableB[HR_ID])
) > 0,
selectedRole = "PU",
CALCULATE(
COUNTROWS(TableA),
TREATAS({currentUser}, TableA[PU_ID])
) > 0,
FALSE
)

This method ensures the slicer value is applied to the correct table for the RLS check.
 

[v-sshirivolu]

Hi @raju8004212 ,

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.

[v-sshirivolu]

Hi @raju8004212 ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[raju8004212]

Hi @v-sshirivolu 

still the problem is not solved because we are not able to pass vailable to rls power bi wont allow to pass variables to rls ,its having a mechanism like rls then slicer not like reversal.

we are still looking for solution to solve the problem.
please connect with me on whatsapp +918341789398 too discuss more on this 

[v-sshirivolu · Accepted solution]

Hi @raju8004212 ,
To clarify it isn’t possible to pass slicer values or variables into RLS. Power BI always applies RLS first (based on user identity), and only afterwards slicers/filters.

That means a “toggle RLS by slicer” design isn’t supported. The only supported approaches are:

Dynamic RLS using USERPRINCIPALNAME()  and a security mapping table.

Report-level toggles (disconnected slicer + measures/filters) to switch between PU/HR views within the user’s authorized data.

Separate roles/reports if different security contexts are mandatory.

So the workarounds I shared earlier will help you simulate the toggle experience, but the true RLS logic can’t be driven by slicers.

[v-sshirivolu]

Hi @raju8004212 ,
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

[MohamedFowzan1]

Hi @raju8004212 

How about doing this with a disconnected table instead:

Type

PU

HR

Now you can use this as the slicer

For bu_org:

IF (
    SELECTEDVALUE('RLS_Type_Switch'[Type]) = "PU",
    WC_BU_ORG_DH[PU_VALUE] IN 
        CALCULATETABLE(
            VALUES(Union_table[ANCESTOR_KEY]),
            FILTER(
                Union_table,
                UPPER(Union_table[EMAIL_ADDRESS]) = UPPER(USERPRINCIPALNAME())
            )
        ),
    TRUE()
)

 
For Snap_D:

IF (
    SELECTEDVALUE('RLS_Type_Switch'[Type]) = "HR",
    WC_EMPLOYEE_snap_D[ANCESTOR_KEY] IN 
        CALCULATETABLE(
            VALUES(Union_table[ANCESTOR_KEY]),
            FILTER(
                Union_table,
                UPPER(Union_table[EMAIL_ADDRESS]) = UPPER(USERPRINCIPALNAME())
            )
        ),
    TRUE()
)

[raju8004212]

Hi @MohamedFowzan1 

I'm not able to pass slicer values into rls whether it's a disconnected/ same table that's main problem I tried that as well it's not  working thanks for checking I'm really looking for some help here to identify a way to make it working.

[MohamedFowzan1]

I did some digging, and it looks like there’s no direct way to have a slicer function like that in conjunction with RLS. The only possible approach would be a workaround without using any RLS, but that would be purely for user experience and not enforce any real security.

In terms of RLS, the proper method is still to define distinct roles and apply the appropriate filters based on user condition essentially assigning users to the roles that match their access requirements.

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

[raju8004212]

Hi @FBergamaschi ,

sample data for table --a
sample data for table--b

sample data for fact table

data model is like

sample visual like below slicer one came from table a slicer 2 came from table b metrics from table c(fact)

now im creating 2 security roles to restrict table a data and table b data based on rls filter

1.who ever logged their pu data only they can see
2.who ever logged their reportee data only they can see
we want to enable the role switch mechanism in report to which role they want see data in report like hr or pu using toggle or slicer

[raju8004212]

Hi @FBergamaschi 

It's just like I have a mechanism where a connected c and b connected to c

Here a is dimension and b is dimension c is fact

My scenario like I have rls on both and b 

But I need slicer selection to select which rls I need to choose

Like slicer shows a 

Then all visuals will be filter for users level access to table a

If slicer shows b

Then visuals will be filtered to rls level access to table b 

It's like switch the rls and enable the user to which rls data they want to see