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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.