Y

Power BI · Advanced

Row-level security, refresh and workspaces

3 min read

Why it matters

At the Architect floor you are not building a report for yourself; you are running a product that a sales organisation opens every morning. Senior analyst and BI developer job descriptions ask for "row-level security", "scheduled refresh", "gateway" and "deployment pipelines". Getting these wrong leaks data or shows yesterday's numbers in a board meeting.

The concept

Row-level security (RLS) filters data by who is viewing. You define roles with DAX filters on dimension tables, then assign users or groups to roles in the Power BI service.

  • Static RLS: one role per region, with a hard-coded filter.

  • Dynamic RLS: one role, filtered by the viewer's identity with USERPRINCIPALNAME() against a mapping table. Scales to any number of people.

Refresh: the service re-imports data on a schedule. Cloud sources refresh directly; on-premises sources (a SQL Server in the office) need an on-premises data gateway.

Workspaces and deployment pipelines separate development, test and production, so a half-finished change never reaches users.

Worked example: dynamic RLS by country

Add a table UserCountry that maps email addresses to the countries they may see:

EmailCountry
priya@example.comUnited Kingdom
arjun@example.comGermany
arjun@example.comFrance

Relate UserCountry[Country] to Customer[Country] (many to one, single direction from UserCountry), or keep it unrelated and filter with DAX. The DAX filter approach is easier to reason about:

rls_role_filter.dax
-- Role "Country managers", filter on the Customer table:
Customer[Country]
    IN CALCULATETABLE (
        VALUES ( UserCountry[Country] ),
        UserCountry[Email] = USERPRINCIPALNAME ()
    )

Test it in Power BI Desktop with Modeling, View as, pick the role and type arjun@example.com as the other user. The Country slicer should show only France and Germany, and total revenue should drop to about 0.44 million (Germany 228,867 plus France 209,042).

Then publish, open the dataset's Security page in the service, and add the managers' security group to the role. Assign groups, not individuals, so joiners and leavers are handled by IT.

Common mistakes

  • Testing only as yourself. Authors see everything. Always test "View as" with a real restricted user.

  • Filtering the fact table in the role. Filter dimensions; the relationship carries the filter to facts and keeps the role fast.

  • Refresh with personal credentials. When you leave the company, the refresh breaks. Use a service account or the gateway's stored credentials.

  • Editing the production report directly. Use a development workspace and a deployment pipeline.

Interview angle

"How would you make sure each regional manager only sees their region?" Answer with dynamic RLS, a mapping table, group-based assignment and "View as" testing, and mention that workspace roles bypass RLS. That last detail tells the interviewer you have run this in production.

Practice task

  1. Implement the UserCountry role and test three users.

  2. Add a page-level message showing USERPRINCIPALNAME() so users know which identity the report sees.

  3. Write a one-page runbook: refresh schedule, gateway owner, what to do when refresh fails.

Further reading