Dynamic Row level security

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

Hi raju8004212 

How about doing this with a disconnected table instead:

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()
)

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

Union_table: stores role-to-user mapping
EMAIL_ADDRESS | TYPE | ANCESTOR_KEY
[email protected] | PU | 1001
[email protected] | HR | 3001
[email protected] | 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 [email protected]. 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.

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. 

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?usp=header]

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]