Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AshPeep
Regular Visitor

RLS When User Key Appears in either To OR From Columns

Background: I have Dynamic RLS sucessfully set up across a broad number of workforce tables using the branch (org code) and the supervisor's UPN to limit access for our department workforce data to only the supervisors of each org code. It works fantastic for 99% of my tables because Org is always a single column.

However, in the 3rd example table I have at the bottom, I have an Org TO column and an Org FROM column for tracking temporary assignments between orgs.

I need my managers to be able to view data from that table for employees on temporary assignment both into and from their org as there are responsibilities involved for both the receiving org and the losing org supervisors. I've only been able to model the relationship from the Supervisor Row Access Table to the Temporary Assignment table using one or the other column, and so far the advice I've turned up online is to create separate tables for Assignments IN and Assignments OUT. This is possible, but it would mean much, much more work as this data affects several other visuals in our reports. 

What I'm hoping for: Is there a way for me to set up a conditional relationship between the Supervisor Row Access Table and the Temporary Assignment Table where the supervisors have access to rows where their Org appears in either the Org From column OR the Org To column? Any help is appreciated!

Supervisor Row Access Table:

UserOrgUPN
Supv NameBranch They SuperviseEmail
NameBranch They SuperviseEmail

 

Sample basic workforce table 

OrgEmployeeGrade
BranchEmployee NameGS Level
BranchEmployee NameGS Level

 

Temporary Assignment Table

Org FROMEmployeeOrg TOEffective Date
BranchEmployee NameBranchDate
BranchEmployee NameBranch 

Capture.JPG

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@AshPeep,

 

Add this DAX to the role for table Temporary Assignment:

 

[Org FROM]
    IN SELECTCOLUMNS (
        FILTER (
            'Supervisor Row Access',
            'Supervisor Row Access'[UPN] = USERPRINCIPALNAME ()
        ),
        "Org", [Org]
    )
||
[Org TO]
    IN SELECTCOLUMNS (
        FILTER (
            'Supervisor Row Access',
            'Supervisor Row Access'[UPN] = USERPRINCIPALNAME ()
        ),
        "Org", [Org]
    )

 

Temporary Assignment should not have a relationship with Supervisor Row Access (otherwise it would cause filtering to occur on only the column in the relationship).





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
DataInsights
Super User
Super User

@AshPeep,

 

Add this DAX to the role for table Temporary Assignment:

 

[Org FROM]
    IN SELECTCOLUMNS (
        FILTER (
            'Supervisor Row Access',
            'Supervisor Row Access'[UPN] = USERPRINCIPALNAME ()
        ),
        "Org", [Org]
    )
||
[Org TO]
    IN SELECTCOLUMNS (
        FILTER (
            'Supervisor Row Access',
            'Supervisor Row Access'[UPN] = USERPRINCIPALNAME ()
        ),
        "Org", [Org]
    )

 

Temporary Assignment should not have a relationship with Supervisor Row Access (otherwise it would cause filtering to occur on only the column in the relationship).





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




This is fantastic, thank you so much! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors