Multiple Dynamic RLS in One PBIX

[etane]

Hello.

I have a single PBIX file where I already have Dynamic RLS successfully implemented for Regional Managers.  So, each Regional Manager can only see sales transactions in their own regions.

We're going to allow Territory Managers to access the same report.  But, we only want each Territory Manager to see their own territory's sales.  So, I implemented Dynamic RLS at the territory level exactly the same way I did for the regional level:

Table view:

Manage Security Role view:

After the Territory level RLS is implemented, I tested View As by entering a territory manager's email and all data remained hidden.  However, when I entered a regional manager's email, the RLS functions as normal.  And, when I disconnected the RLS_RSM table from the Sales table, the territory manager's RLS starts working.  

So, it seems I can't use two Dynamic RLS in the same PBIX at least not in the manner I am using it.  

Is there a way to use two sets of Dynamic RLS in the same PBIX file, so that both the Regional and the Territory Dynamic RLS work?

Thanks.

[MarkLaf]

Here is a specific example of using dynamic RLS to achieve something like what I think you are going for.

Here, rather than associating people with Sales, we're associating the territory (and region) from a dimension instead. Then, we relate a Security table to our Territory dimension and apply the RLS filter to Territory, ensuring that the user only sees data from Sales and Security that are related to the territory they have access to.

Tables

Sales

Transaction#	TerritoryId	Sales	Date
1	1	100	1/1/2025
2	1	200	1/2/2025
3	1	300	1/3/2025
4	2	400	1/4/2025
5	5	500	1/5/2025
6	5	600	1/6/2025
7	4	700	1/7/2025
8	4	800	1/8/2025

Territory

Id	Territory Name	Region
1	A	East
2	B	East
3	C	East
4	D	West
5	E	West
6	F	West

Security

Name	Email	Role	TerritoryAccess
Cindy Bay	[email protected]	Territory Manager	1
Cray Bot	[email protected]	Territory Manager	2
Andy Ann	[email protected]	Territory Manager	null
Lola Long	[email protected]	Territory Manager	4
Mike Blue	[email protected]	Territory Manager	5
Peter Bill	[email protected]	Territory Manager	null
Bob Lee	[email protected]	Regional Manager	1
Bob Lee	[email protected]	Regional Manager	2
Bob Lee	[email protected]	Regional Manager	3
Roger Smith	[email protected]	Regional Manager	4
Roger Smith	[email protected]	Regional Manager	5
Roger Smith	[email protected]	Regional Manager	6

Model

How you can still associate RSM and TM to Sales with measures

Territory Manager = 
IF( 
    ISINSCOPE( Territory[Territory Name] )
    && NOT ISEMPTY( Sales ), 
    CALCULATE( 
        CONCATENATEX( VALUES( Security[Name] ), Security[Name], ";" ), 
        Territory, 
        Security[Role] = "Territory Manager" 
    ) 
)

Regional Manager = 
IF( 
    ISINSCOPE( Territory[Region] )
    && NOT ISEMPTY( Sales ), 
    CALCULATE( 
        CONCATENATEX( VALUES( Security[Name] ), Security[Name], ";" ), 
        Territory, 
        Security[Role] = "Regional Manager" 
    ) 
)

RLS

In action

[etane · Accepted solution]

Thanks.  One advantage, if it could be done this way that is, with two Dynamic RLS tables is I don't need to assign individual territories to each regional manager.  It's an advantage in that when territory alignments change, I don't need to reassign territories in the Security table.  And, in my salesforce dataset, the sales rep is the territory, and with high turnover, the Security table has to be constantly updated.  

I could load territories to the fact table to overcome this.  I know this quark might be unique to my dataset.  But, your answer is very spot on otherwise.

[MarkLaf]

If it were me, I'd go with the structure in my last post, and use Power Query transformations to get the related territories associated to the RMs before loading in (merge on Reports To, etc.). Although, perhaps there are reasons you would not or could not do that.

Just because I like playing around with different RLS scenarios, here is a setup where Security uses a ReportsTo column to indirectly relate Regional Managers to territories (aka Territory Managers, per your confirmation). As noted in some comments below, since this is just two levels, we can avoid using PATH functions, which are typically needed for traversing multiple levels of a hierarchy.

Tables

Sales

(same)

Transaction#	TerritoryId	Sales	Date
1	1	100	1/1/2025
2	1	200	1/2/2025
3	1	300	1/3/2025
4	2	400	1/4/2025
5	5	500	1/5/2025
6	5	600	1/6/2025
7	4	700	1/7/2025
8	4	800	1/8/2025

Territory

(name now match managers)

Id	Territory Name	Region
1	C. Bay	East
2	C. Bot	East
3	A. Ann	East
4	L. Long	West
5	M. Blue	West
6	P. Bill	West

Security

(RM now has no territory assignment, we rely on TM's ReportTo instead)

Id	Name	Email	Role	ReportsTo
1	Cindy Bay	[email protected]	Territory Manager	7
2	Cray Bot	[email protected]	Territory Manager	7
3	Andy Ann	[email protected]	Territory Manager	7
4	Lola Long	[email protected]	Territory Manager	8
5	Mike Blue	[email protected]	Territory Manager	8
6	Peter Bill	[email protected]	Territory Manager	8
7	Bob Lee	[email protected]	Regional Manager	null
8	Roger Smith	[email protected]	Regional Manager	null

Model

(Territory -> Security relationship is now inactive as we only need it in a few select cases. Relationship is now Territory[Id] -1--*-> Security[Id])

How to get associate RSM with measure

(Don't need to look up TM name from Security if we can just use their name from Territory)

Regional Manager = 
VAR _TerritoryManagerReportsToID = 
    CALCULATE( 
        VALUES( Security[ReportsTo] ), 
        USERELATIONSHIP( Territory[Id], Security[Id] ) 
    )
RETURN
IF( 
    ISINSCOPE( Territory[Region] )
    && NOT ISEMPTY( Sales ), 
    CALCULATE( 
        VALUES( Security[Name] ),
        TREATAS( { _TerritoryManagerReportsToID }, Security[Id] )
    )
)

RLS

(Since we aren't solely relying on an active physical relationship to propogate RLS filter from Territory to Security, we also need an RLS filter on Security. DAX is a little more verbose, so including DAX part in code blocks.)

Filter Data (Security)

// Since this is two-level hierarchy, avoiding PATH functions
// For 2+ levels, would need to use PATH functions, probably
VAR _filt = 
    CALCULATETABLE( 
        Security, 
        REMOVEFILTERS( Security ), 
        TREATAS( { USERPRINCIPALNAME() }, Security[Email] ) 
    )
VAR _myId = 
    CALCULATE( VALUES( Security[Id] ), _filt )
VAR _myManagersId = 
    CALCULATE( VALUES( Security[ReportsTo] ), _filt )
VAR _myDirectReportsIds =
    CALCULATETABLE( 
        VALUES( Security[Id] ),
        Security[ReportsTo] = _myId
    )
VAR _combinedIds =
    UNION(
        { _myId, _myManagersId },
        _myDirectReportsIds
    )
RETURN
Security[Id] IN _combinedIds

Filter Data (Territory)

// Since this is two-level hierarchy, avoiding PATH functions
// For 2+ levels, would need to use PATH functions, probably
VAR _curID = 
    CALCULATE( 
        VALUES( Security[Id] ), 
        TREATAS( { USERPRINCIPALNAME() }, Security[Email] ) 
    )
RETURN
CALCULATE( 
    NOT ISEMPTY( Security ), 
    FILTER(
        Security,
        Security[ReportsTo] = _curID
        || Security[Id] = _curID
    ),
    USERELATIONSHIP( Territory[Id], Security[Id] )
)

In Action

[tharunkumarRTK]

@etane 

Instead of complicating the logic by creating two RLS tables, please consolidate it to one RLS table and one RLS role. 

Within the role, implement your DAX logic in such a way the you can check whether the logged in user is a regional manger or territorial manager, depending on the result of this condition, return the filter condition. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[lbendlin]

You are using static RLS.  Dynamic RLS has a single role and a reference table with permissions for each individual users.

Change to Dynamic RLS.