26. Row-Level Security (RLS)

26.3 Dynamic RLS with USERPRINCIPALNAME in Power BI, with Example

By Ravindra Bagale · Last updated: 6 October 2026

Start with a problem

Next year there will be 60 cities. Ravindra cannot keep creating roles.

Explain simply

Dynamic RLS uses a security table (Email → City) and USERPRINCIPALNAME() to filter by the signed-in user.

Show the data

About this data: This table uses columns: Email, City. This table uses columns: User (email), City in security table, Rows visible, Total Sales (₹). This table uses columns: Other user, Cities visible. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: rls_user_security.csv, visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

Show the result

Your result should look like this (fictional sample):

Explain what happened

USERPRINCIPALNAME returns the email; the matching Security rows filter City, which filters Orders.

Explore further

• Add a new manager by adding a row – no model change.

Detailed reference for this lesson:

First look at the data (before → after):

[City] IN CALCULATETABLE(VALUES('User Security'[City]),
    'User Security'[Email] = USERPRINCIPALNAME())

USERPRINCIPALNAME() returns the sign-in name of the current user, usually their e-mail-style address (UPN), such as shraddha.bagale@example.com. The rule compares this to a column in the data.

Simple version – one manager per city

The DarkStore table has a Manager Email column (for example, every Pune store row has ravindra.bagale@example.com, every Nashik row has shraddha.bagale@example.com). Create one role called City Manager with this rule on the DarkStore table:

[Manager Email] = USERPRINCIPALNAME()

Now every manager, when signed in, sees fakt the dark stores – and therefore only the orders – where their e-mail matches.

Flexible version – a security (mapping) table

When a user may see several cities, or a city has several users, create a table User Security:

Here Ravina (a regional lead in this example) can see both Pune and Mumbai.

Option A – without any relationship to the security table, use this rule on DarkStore:

[City]
    IN CALCULATETABLE(
        VALUES('User Security'[City]),
        'User Security'[Email] = USERPRINCIPALNAME()
    )

Option B – relate User Security to a City dimension, filter 'User Security'[Email] = USERPRINCIPALNAME(), and allow the filter to flow onwards (this usually requires a bi-directional relationship with Apply security filter in both directions enabled). Option A avoids bi-directional relationships and is usually simpler.

Hide the User Security table from report view (users do not need to see it).

A role that sees everything

For the state operations head (Rani in our sample), create a role (e.g. All Cities) with no filter – or give access in a way where RLS does not apply (see below).

Fix common mistakes

Practise independently

Think like a professional

Dynamic RLS is the scalable pattern – explain table + UPN + relationship.

Ravi Sir's Cards for this lesson

Revision cardInterview card

मराठी cards