Mastering DAX Security: Implementing Dynamic Row-Level Security with USERPRINCIPALNAME and Complex Role Hierarchies

Introduction

Picture this: your company has just deployed a regional sales dashboard in Power BI Service. The VP of Sales loves it. Regional managers love it. Then someone notices that a rep in the Chicago office can see revenue figures for the entire West Coast territory — including a competitor's account that's mid-negotiation. You now have a data governance incident on your hands, and the fix needed yesterday.

Row-Level Security (RLS) is Power BI's answer to this problem, but the gap between "I've set up a basic RLS role" and "I've built a security model that survives contact with a real organization" is wider than most tutorials acknowledge. Static roles — where you manually assign users to fixed filters — break the moment your org chart changes, your team doubles in size, or your business logic requires a manager to see everything their direct reports see. What you actually need is dynamic RLS: a system where the security rules enforce themselves based on who is asking, pulling that logic from your data rather than from a hard-coded filter list.

By the end of this lesson, you'll be able to design and implement a production-grade dynamic RLS system in Power BI using DAX. We'll work through realistic organizational hierarchy scenarios, handle the messy edge cases, and give you the debugging tools to verify your security model actually works before it goes live.

What you'll learn:

• How USERPRINCIPALNAME() works at runtime and why it's the foundation of dynamic RLS
• How to structure a security mapping table that supports both flat and hierarchical access
• How to write DAX role filters that handle manager-subordinate relationships using PATH and related functions
• How to handle multi-role users, cross-functional access, and exceptions without duplicating logic
• How to test, audit, and troubleshoot your security model before it reaches production

---

Prerequisites

You should already be comfortable with:

• Basic RLS concepts in Power BI (creating a role, assigning users in Power BI Service)
• Core DAX functions: FILTER, CALCULATE, RELATED, VALUES
• Data modeling fundamentals: relationships, lookup tables, star schema
• Power BI Service publishing and workspace management

If you haven't built a basic static RLS role before, take twenty minutes to do that first. This lesson builds directly on top of that foundation.

---

Understanding the Runtime Context of USERPRINCIPALNAME

Before writing a single line of DAX, you need to understand when and where USERPRINCIPALNAME() executes — because if you get this wrong, your security model will silently fail in ways that are difficult to diagnose.

USERPRINCIPALNAME() is a DAX function that returns the email address of the currently authenticated user as a string. When a report is viewed in Power BI Service, that string is the user's Azure Active Directory (AAD) UPN — typically their work email, like sarah.chen@contoso.com. When you're in Power BI Desktop and you use Modeling > View as Role, Power BI substitutes the email you type in the "Other user" field.

Here's the critical thing: USERPRINCIPALNAME() is evaluated inside the row filter expression of a role. The filter expression runs once per row of the table it's applied to, during query execution. It doesn't run at model load time. It doesn't cache results. Every time a user opens a report page that triggers a DAX query against a secured table, the filter evaluates fresh.

This means your role filter expression has to be a boolean expression — something that resolves to TRUE or FALSE for each row — and it has to do that efficiently, because it runs in the filter context of every query against that table.

A static role filter looks like this:

[Region] = "Midwest"

A dynamic role filter replaces the hard-coded value with a lookup against a security table:

[Region] IN
    CALCULATETABLE(
        VALUES(UserRegionAccess[Region]),
        UserRegionAccess[UserPrincipalName] = USERPRINCIPALNAME()
    )

This is the conceptual shift. Instead of storing the rule in the DAX, you store it in the data. The DAX just asks the question: "Does the current user have access to the region on this row?"

---

Designing the Security Mapping Table

The quality of your dynamic RLS is almost entirely determined by the design of your security mapping table. This is where most implementations go wrong — not in the DAX, but in the data structure underneath it.

Flat Access: The Starting Point

For a simple scenario — say, each sales rep has access to exactly one region — a flat mapping table works fine:

UserPrincipalName	Region
sarah.chen@contoso.com	midwest
james.okafor@contoso.com	northeast
priya.sharma@contoso.com	west
michael.torres@contoso.com	midwest

The role filter on your Sales table (assuming it has a Region column, or joins to a Geography table that does) becomes:

[Region] IN
    CALCULATETABLE(
        VALUES(UserAccess[Region]),
        FILTER(
            UserAccess,
            LOWER(UserAccess[UserPrincipalName]) = USERPRINCIPALNAME()
        )
    )

This works. But it has no concept of hierarchy. A regional manager assigned to "midwest" sees exactly the same data as a rep assigned to "midwest." There's no way for a director to see everything without being manually added to every single region.

Adding Hierarchy: The Organization Table

Real organizations have managers. A manager needs to see everything their team sees. A director needs to see everything their managers see. This is where flat mapping tables break down and you need to model the hierarchy explicitly.

The cleanest approach uses two separate tables:

EmployeeHierarchy — your org chart:

EmployeeID	EmployeeName	UserPrincipalName	ManagerID	JobLevel
1001	Sarah Chen	sarah.chen@contoso.com	1005	Rep
1002	James Okafor	james.okafor@contoso.com	1005	Rep
1003	Priya Sharma	priya.sharma@contoso.com	1006	Manager
1004	Michael Torres	michael.torres@contoso.com	1005	Rep
1005	Diana Park	diana.park@contoso.com	1007	Manager
1006	Carlos Reyes	carlos.reyes@contoso.com	1007	Manager
1007	Fatima Al-Hassan	fatima.alhassan@contoso.com	NULL	Director

UserAccess — the direct assignment of employees to data:

EmployeeID	Region
1001	midwest
1002	northeast
1003	west
1004	midwest

In this model, reps are directly assigned to regions. Managers and directors don't need to be in the access table — instead, we'll use DAX to walk the hierarchy and determine which employees report (directly or indirectly) to the current user, then aggregate their access permissions.

---

Using PATH Functions to Walk the Hierarchy

DAX's PATH family of functions exists specifically for parent-child hierarchies stored in relational tables. You'll use these to build the backbone of your manager-subordinate security logic.

Building the PATH Column

First, add a calculated column to EmployeeHierarchy that encodes the full ancestor chain for each employee:

EmployeePath =
PATH(
    EmployeeHierarchy[EmployeeID],
    EmployeeHierarchy[ManagerID]
)

For Sarah Chen (EmployeeID 1001, whose chain goes 1001 → 1005 → 1007), this produces the string "1007|1005|1001". The PATH function always encodes from root to leaf. For Fatima Al-Hassan (the director at the top), it produces just "1007".

Finding All Subordinates of the Current User

Now we can write the core logic: given the logged-in user, which employee IDs are in their subtree?

Here's the DAX measure that returns the set of EmployeeIDs accessible to the current user:

AccessibleEmployeeIDs =
VAR CurrentUPN = USERPRINCIPALNAME()

VAR CurrentEmployeeID =
    CALCULATE(
        SELECTEDVALUE(EmployeeHierarchy[EmployeeID]),
        FILTER(
            ALL(EmployeeHierarchy),
            LOWER(EmployeeHierarchy[UserPrincipalName]) = CurrentUPN
        )
    )

VAR SubordinateIDs =
    CALCULATETABLE(
        VALUES(EmployeeHierarchy[EmployeeID]),
        FILTER(
            ALL(EmployeeHierarchy),
            PATHCONTAINS(
                EmployeeHierarchy[EmployeePath],
                CurrentEmployeeID
            )
        )
    )

RETURN SubordinateIDs

PATHCONTAINS(path, value) returns TRUE if the path string contains the given value. Since a manager's ID appears in the path of every employee beneath them, this one expression captures the entire subtree — the manager themselves, their direct reports, their direct reports' reports, and so on, no matter how deep the hierarchy goes.

Translating Subordinate IDs to Data Access

With the subordinate ID set in hand, the RLS filter on your Sales table becomes a join against the UserAccess table:

[EmployeeID] IN
    CALCULATETABLE(
        VALUES(UserAccess[EmployeeID]),
        FILTER(
            UserAccess,
            UserAccess[EmployeeID] IN
                CALCULATETABLE(
                    VALUES(EmployeeHierarchy[EmployeeID]),
                    FILTER(
                        ALL(EmployeeHierarchy),
                        PATHCONTAINS(
                            EmployeeHierarchy[EmployeePath],
                            CALCULATE(
                                SELECTEDVALUE(EmployeeHierarchy[EmployeeID]),
                                FILTER(
                                    ALL(EmployeeHierarchy),
                                    LOWER(EmployeeHierarchy[UserPrincipalName]) = USERPRINCIPALNAME()
                                )
                            )
                        )
                    )
                )
        )
    )

This is getting complex. Let's refactor this using a technique that makes RLS DAX dramatically more maintainable.

---

Refactoring with a Security Bridge Table

Rather than nesting all of this logic inside the role filter — which is painful to maintain and can hit performance limits — build a security bridge table as a calculated table that materializes the full user-to-employee mapping at model refresh time.

SecurityBridge =
ADDCOLUMNS(
    CROSSJOIN(
        SELECTCOLUMNS(
            EmployeeHierarchy,
            "AccessorUPN", LOWER(EmployeeHierarchy[UserPrincipalName]),
            "AccessorID", EmployeeHierarchy[EmployeeID],
            "AccessorPath", EmployeeHierarchy[EmployeePath]
        ),
        SELECTCOLUMNS(
            EmployeeHierarchy,
            "TargetID", EmployeeHierarchy[EmployeeID],
            "TargetPath", EmployeeHierarchy[EmployeePath]
        )
    ),
    "HasAccess",
        PATHCONTAINS([AccessorPath], [TargetID])
        || PATHCONTAINS([TargetPath], [AccessorID])
)

Wait — that's overcomplicated and would produce a huge table. Let me give you the cleaner version that only keeps the rows where access is granted:

SecurityBridge =
FILTER(
    ADDCOLUMNS(
        CROSSJOIN(
            SELECTCOLUMNS(
                EmployeeHierarchy,
                "AccessorUPN", LOWER(EmployeeHierarchy[UserPrincipalName]),
                "AccessorID", EmployeeHierarchy[EmployeeID],
                "AccessorPath", EmployeeHierarchy[EmployeePath]
            ),
            SELECTCOLUMNS(
                EmployeeHierarchy,
                "TargetID", EmployeeHierarchy[EmployeeID]
            )
        ),
        "HasAccess", PATHCONTAINS([AccessorPath], [TargetID])
    ),
    [HasAccess] = TRUE()
)

This table has one row for every (accessor, target) pair where the accessor has hierarchical access to the target. For a 200-person org with reasonable depth, this table has tens of thousands of rows — completely manageable for a model refresh.

Now your role filter becomes dramatically simpler:

[EmployeeID] IN
    CALCULATETABLE(
        VALUES(SecurityBridge[TargetID]),
        FILTER(
            SecurityBridge,
            SecurityBridge[AccessorUPN] = USERPRINCIPALNAME()
        )
    )

This is fast, readable, and maintainable. All the hierarchy logic lives in the calculated table, computed once at refresh. The runtime filter is a simple lookup.

---

Handling Complex Role Scenarios

Real organizations throw curveballs that break simple hierarchy models. Here are the three most common ones and how to handle them.

Scenario 1: Users with Access Outside Their Hierarchy

A business analyst might need to see all regions for reporting purposes without being anyone's manager. An auditor might need read access to everything. A newly promoted manager might temporarily need their old peer's data while transitioning accounts.

Handle these with an exception access table:

UserPrincipalName	TargetEmployeeID	Reason	ExpiresDate
analyst@contoso.com	ALL	Reporting role	NULL
auditor@contoso.com	ALL	Compliance audit	2025-03-31
diana.park@contoso.com	1003	Transition period	2025-02-15

In your security bridge calculation, union this exception table's rows in after filtering for non-expired grants:

ExceptionAccess =
FILTER(
    SELECTCOLUMNS(
        ExceptionAccessTable,
        "AccessorUPN", LOWER(ExceptionAccessTable[UserPrincipalName]),
        "TargetID", ExceptionAccessTable[TargetEmployeeID]
    ),
    OR(
        ISBLANK(ExceptionAccessTable[ExpiresDate]),
        ExceptionAccessTable[ExpiresDate] >= TODAY()
    )
)

Then in the role filter, combine the security bridge with the exception access:

[EmployeeID] IN
    UNION(
        CALCULATETABLE(
            VALUES(SecurityBridge[TargetID]),
            SecurityBridge[AccessorUPN] = USERPRINCIPALNAME()
        ),
        CALCULATETABLE(
            VALUES(ExceptionAccess[TargetID]),
            ExceptionAccess[AccessorUPN] = USERPRINCIPALNAME()
        )
    )

For the "ALL" case, where TargetEmployeeID is the string "ALL", you handle it separately:

VAR HasAllAccess =
    COUNTROWS(
        FILTER(
            ExceptionAccess,
            ExceptionAccess[AccessorUPN] = USERPRINCIPALNAME()
                && ExceptionAccess[TargetID] = "ALL"
        )
    ) > 0

RETURN
    IF(
        HasAllAccess,
        TRUE(),
        [EmployeeID] IN
            CALCULATETABLE(
                VALUES(SecurityBridge[TargetID]),
                SecurityBridge[AccessorUPN] = USERPRINCIPALNAME()
            )
    )

Scenario 2: Multi-Dimensional Security

Some organizations need to secure data along multiple axes simultaneously. A regional manager might need to see all data for their region but only for the products in their division's catalog.

The key principle: each dimension of security gets its own filter, and they compound. Apply a filter to the Geography table for regional access and a separate filter to the Product table for product access. Because Power BI propagates both filters through relationships to the Sales fact table, the user only sees intersecting data — rows that pass both security filters.

Create separate role filters for each secured dimension:

On Geography:

[RegionCode] IN
    CALCULATETABLE(
        VALUES(UserRegionAccess[RegionCode]),
        UserRegionAccess[UserPrincipalName] = USERPRINCIPALNAME()
    )

On Product:

[ProductDivision] IN
    CALCULATETABLE(
        VALUES(UserProductAccess[Division]),
        UserProductAccess[UserPrincipalName] = USERPRINCIPALNAME()
    )

Scenario 3: Object-Level Security vs. Row-Level Security

Sometimes the requirement isn't "filter rows" but "hide this column entirely." Power BI supports Object-Level Security (OLS) for this, but it's separate from RLS and configured via Tabular Editor or XMLA endpoints, not through the standard Security dialog. Don't try to implement column hiding through RLS filters — you'll make the model fragile and confusing. Use the right tool for the right problem.

---

Applying the Filter to the Right Table in Your Model

A common mistake is applying the RLS filter to the fact table directly. In a well-structured star schema, your fact table has foreign keys — not the descriptive values the business thinks about. If a user's access is defined in terms of Region, and Region lives in a Geography dimension table, filter the Geography table and let the relationship propagate to the Sales fact table.

Here's the model relationship that makes this work:

• Sales[GeographyKey] → Geography[GeographyKey] (Many-to-One, Single direction from Sales to Geography)
• RLS filter is applied on Geography, not on Sales

When Power BI evaluates a query against Sales, it applies the Geography filter first (because of RLS), then uses the relationship to restrict which Sales rows are visible. This is cleaner, more maintainable, and usually more performant than filtering the fact table directly.

Bidirectional relationships and RLS don't mix well. If you've set up bidirectional cross-filtering on any relationship in your model, RLS filters may not propagate the way you expect — or worse, they may propagate in directions that expose data they shouldn't. Audit every bidirectional relationship in a secured model carefully, and use CROSSFILTER in measures instead of model-level bidirectional settings wherever possible.

---

Hands-On Exercise: Building the Complete Security Model

Let's put everything together in a structured exercise. You'll build a working security model for a fictional company, Contoso Industrial, with regional sales data.

Step 1: Create the Data Tables in Power Query

In Power BI Desktop, use "Enter Data" to create these tables manually (or load them from CSV):

EmployeeHierarchy with columns: EmployeeID (whole number), EmployeeName (text), UserPrincipalName (text, all lowercase), ManagerID (whole number, allow null), JobLevel (text)

Populate it with at least 8 employees: 1 director, 2 managers reporting to the director, and 2-3 reps reporting to each manager.

SalesData with columns: SaleID, EmployeeID, Region, Amount, SaleDate

UserAccess with columns: EmployeeID, Region

Step 2: Add the EmployeePath Calculated Column

In your EmployeeHierarchy table, add:

EmployeePath = PATH(EmployeeHierarchy[EmployeeID], EmployeeHierarchy[ManagerID])

Step 3: Create the SecurityBridge Calculated Table

In Modeling > New Table:

SecurityBridge =
FILTER(
    ADDCOLUMNS(
        CROSSJOIN(
            SELECTCOLUMNS(
                EmployeeHierarchy,
                "AccessorUPN", LOWER(EmployeeHierarchy[UserPrincipalName]),
                "AccessorID", EmployeeHierarchy[EmployeeID],
                "AccessorPath", EmployeeHierarchy[EmployeePath]
            ),
            SELECTCOLUMNS(
                EmployeeHierarchy,
                "TargetID", EmployeeHierarchy[EmployeeID]
            )
        ),
        "HasAccess", PATHCONTAINS([AccessorPath], [TargetID])
    ),
    [HasAccess] = TRUE()
)

Step 4: Create the RLS Role

Go to Modeling > Manage Roles > New Role. Name it "DynamicEmployeeAccess."

Apply this filter to your SalesData table:

[EmployeeID] IN
    CALCULATETABLE(
        VALUES(SecurityBridge[TargetID]),
        FILTER(
            SecurityBridge,
            SecurityBridge[AccessorUPN] = USERPRINCIPALNAME()
        )
    )

Step 5: Test the Role

Use Modeling > View as > Other User, and type in the UPN of one of your reps. Verify they only see their own sales rows. Then switch to a manager's UPN and verify they see all their direct reports' rows plus their own. Finally, use the director's UPN and confirm they see everything.

Step 6: Verify the SecurityBridge Is Correct

Before publishing, add a temporary page to your report with a table visual on the SecurityBridge table. View as the director — you should see the full matrix. View as a rep — you should see only rows where AccessorUPN matches their email, which should be just the one row pointing to themselves. Delete this page before publishing.

---

Common Mistakes & Troubleshooting

"Users see no data at all after I enable the role."

First suspect: case mismatch between the UPN in your security table and what USERPRINCIPALNAME() returns. Add a card visual showing USERPRINCIPALNAME() to a page in "View as" mode and compare it character-by-character against your table. Second suspect: the user doesn't exist in your EmployeeHierarchy — the SecurityBridge has no rows for them, so the filter returns empty, and no rows pass. Add a default "no match" behavior or ensure every user is accounted for.

"Managers can see too much / too little."

Check your EmployeePath values by adding a table visual showing EmployeeID and EmployeePath. Confirm the paths look correct (root at left, leaf at right, pipe-delimited). If a manager's ID doesn't appear in their reports' paths, the relationship between EmployeeID and ManagerID may be broken — verify data types match and there are no leading/trailing spaces in the ID fields.

"The report is slow after implementing RLS."

The SecurityBridge crossjoin is the common culprit for large orgs. Profile the query using DAX Studio (connect to the PBI Desktop file, run a trace) and look for the SecurityBridge filter evaluation time. If it's high, move the SecurityBridge computation to Power Query as a Python or SQL pre-computation step, and import it as a regular table rather than a calculated table.

"RLS works in Desktop but not in Service."

In Power BI Service, you must assign users to the role explicitly, even for dynamic roles. Go to the dataset settings in the Service, navigate to Security, and add users or Azure AD security groups to the DynamicEmployeeAccess role. The role still does dynamic filtering — you're just telling the Service which users should have the role applied at all. Users not assigned to any role see everything (admin behavior), which is another reason to test carefully before publishing.

"The PATH function returns an error."

This usually means there's a cycle in your hierarchy (employee A has manager B, who has manager A). Clean this in Power Query by checking for circular references before the data reaches the model. Also check for self-references (ManagerID equals EmployeeID), which PATH handles as a root node but can produce unexpected results.

---

Summary & Next Steps

You've now built a dynamic RLS system that does what static roles never could: it adapts to your org chart automatically, enforces manager-subordinate hierarchy without manual maintenance, and provides a clean extension point for exceptions and multi-dimensional access.

The core patterns to carry forward:

• Normalize emails to lowercase in Power Query — never trust mixed-case UPNs at query time.
• Compute hierarchy logic in a calculated table (SecurityBridge) — keep runtime filter expressions simple; move complexity to refresh time.
• Filter dimension tables, not fact tables — let relationships do the propagation work.
• Apply RLS filters on the right table with the right relationship direction — audit bidirectional relationships in any secured model.
• Test with "View as" before publishing — and verify the SecurityBridge data directly, not just the final report output.

Where to go from here:

• Object-Level Security (OLS): Learn how to hide columns and tables from specific user groups using Tabular Editor and XMLA endpoint deployment. OLS and RLS can coexist in the same model and serve different security requirements.
• Azure AD Group-Based Role Assignment: Instead of adding individual users to roles in Power BI Service, assign Azure AD security groups. This moves user management to IT/HR systems where it belongs.
• Dynamic RLS with Direct Query: The SecurityBridge calculated table approach doesn't work in Direct Query mode (no calculated tables). Learn the alternative: pushing RLS logic into SQL views or stored procedures, and using USERNAME() (not USERPRINCIPALNAME()) for certain data source types.
• Auditing and Compliance Logging: Power BI's audit log captures who viewed which reports, but not what rows they saw. Learn how to build a security audit trail using Azure Log Analytics and Power BI activity events.
• Performance Testing at Scale: Use DAX Studio and the VertiPaq Analyzer to measure the memory footprint of your SecurityBridge table and benchmark security filter evaluation against realistic data volumes before go-live.

Security in Power BI is an area where the gap between "it works" and "it works correctly" is expensive to discover in production. The model you've built here gives you both confidence and the tools to verify that confidence is warranted.