26.3 Dynamic RLS With USERPRINCIPALNAME

By Ravindra Bagale Last updated: 25 September 2026

In short: USERPRINCIPALNAME() returns the sign-in name of the current user, usually their e-mail-style address (UPN), such as shraddha.bagale@example.com.

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

Aata he bagha: when a user may see several cities, or a city has several users, create a table User Security:

Email	City
ravindra.bagale@example.com	Pune
shraddha.bagale@example.com	Nashik
shahrukh@example.com	Nagpur
zoya@example.com	Kolhapur
amir@example.com	Solapur
raja@example.com	Sambhaji Nagar
ravina@example.com	Pune
ravina@example.com	Solapur

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

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).

USERNAME vs USERPRINCIPALNAME

USERNAME() in Power BI Desktop returns the Windows format DOMAIN\user, while in the Service it returns the UPN. USERPRINCIPALNAME() returns the UPN format consistently when users are signed in, so it is the usual choice for dynamic RLS.

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).

Dynamic RLS thoda tricky aahe, pan ghabru naka. Security table aani USERPRINCIPALNAME – evdhech don tukde aahet.

Ravindra Bagale's Tip

Friends, the mistake I see most is an e-mail in the security table that doesn't match the user's sign-in name exactly (different case, an old domain or an alias). Check USERPRINCIPALNAME() by showing it in a card while testing, and keep the security table's relationship filtering in the right direction. Practise, and it will feel very easy.