DP-600: implementing security roles in semantic models

I will teach how to implement Row-Level Security (RLS) in semantic models in the context of Power BI/Fabric. This skill is essential for DP-600 because it ensures that users only see the data that pertains to them — a common requirement in enterprise analytics solutions. RLS prevents inadvertent exposures and helps meet internal and external compliance requirements.

What you need to know

Row-Level Security (RLS) restricts which data rows a user can access in a semantic model. In Power BI and Fabric, RLS is applied at the model (tabular) level through roles that contain DAX filters or, in some scenarios, user mappings. There are two main modes:

• Static RLS: roles with fixed rules (e.g.: Sales[Country] = "Germany"). Useful when groups have immutable restrictions — for example, a finance team that only sees data for Germany. It is simple to manage when the number of roles is small (e.g. 5-10 regions).
• Dynamic RLS: uses DAX functions such as USERPRINCIPALNAME() to apply filters based on the accessing user. Essential when permissions depend on user identity or when you have thousands of different users. It allows maintaining only a few roles (e.g.: "RLS_Region") and using a mapping to determine the user's scope.

Simple example of a DAX filter for a role that limits the Sales table by the user's department (assuming a Users mapping table):

Sales[SalesRegion] IN VALUES(Users[Region])

Another common dynamic expression is based on USERPRINCIPALNAME():

Users[UserPrincipalName] = USERPRINCIPALNAME()

The idea is to link the user to their scope through relationships in the model or DAX expressions that query mapping tables. Also remember the differences between USERNAME() and USERPRINCIPALNAME() — the latter is more reliable in Azure AD environments.

In practice

Practical steps to implement RLS in a semantic model in Power BI Desktop / Fabric Model:

1. Prepare user mapping: create a Users table (can be imported) with columns: UserPrincipalName, Region, Department, RoleLevel. In real scenarios you may have 10k-50k rows if the mapping is per employee. Alternatively, use group-based mapping (Azure AD Group) to reduce cardinality.

2. Establish relationships: in the model, connect Users to relevant fact/dimension tables (e.g.: Users[Region] → Sales[SalesRegion]). Use 1:* relationships with single direction preferred from the Users side to facts. Check the filter direction (single vs both) because it can affect performance and filter behavior.

3. Create dynamic role: in Power BI Desktop go to 'Modeling' → 'Manage Roles' and create a new role, for example "RLS_Region". For the Users table or directly on the fact table write a DAX expression using USERPRINCIPALNAME():

   Users[UserPrincipalName] = USERPRINCIPALNAME()

   or, if you want to filter by region via relationship:

   Sales[SalesRegion] IN VALUES(Users[Region])

   If you work with UPNs that may vary in case, normalize with UPPER() or LOWER() on both sides of the mapping.

4. Test roles locally: use the "View as" feature in Power BI Desktop to simulate different users or roles. Test with at least 10 cases: admin, 3 typical users, 3 that have no access (should see 0 rows) and 3 with multi-region access. This validates that filters work before publishing.

5. Publish and assign users: publish to the Fabric/Power BI Service tenant. In the service, go to the dataset or model, open 'Security' and assign users or Azure AD groups to the defined roles. It is recommended to use Azure AD groups (reduces management) and avoid adding hundreds of users manually to a role.

6. Audit and validate: verify with real user accounts (or groups) and use audit logs and Usage Metrics to confirm that accesses are correct. In an environment with 5k reports, configuring anomalous usage alerts helps detect RLS errors.

Common mistakes

• Assuming RLS works without correct relationships: if the Users table is not related correctly to the fact table, filters will not be applied as expected. For example, an incorrect relationship can result in 100% of the data being visible instead of 0%.
• Using USERPRINCIPALNAME() without ensuring the correct format: in environments with federated identities, the value of USERPRINCIPALNAME() may not exactly match the UserPrincipalName column; normalize case and formats (upn vs email) in the mapping.
• Testing only with admins: administrators often see more data; test with normal accounts or use the "View as" feature to simulate real users. Also test complex measures (time intelligence) to ensure the filter context does not break calculations.
• Neglecting performance: complex filters on models with millions of rows can reduce performance. Use indexes on the backend when possible, simplify DAX expressions and evaluate DirectQuery vs Import.

How to practice

Practice by implementing roles in different scenarios: by region, by line of business, and with inheritance via hierarchies (e.g.: country → region → store). Create a fictitious dataset with tables Users (5k rows), Sales (1M rows), Product and Region. Implement one dynamic role and one static role, test queries and measure response time (e.g. 200 ms vs 2s after applying RLS).

Useful tools: DAX Studio to analyze queries and SQL Profiler to diagnose DirectQuery. For official training, use the OFFICIAL and free Practice Assessment from Microsoft and consult the free DP-600 study guide in Microsoft documentation — both are official and free resources I recommend for practical preparation and review of the measured skills.

In summary

• RLS protects data at the row level; it can be static or dynamic.
• Models need mapping tables and correct relationships for RLS to work.
• USERPRINCIPALNAME() is the key function for dynamic RLS, but normalize identities.
• Testing locally and in the service is crucial; use Azure AD groups to manage assignments at scale.