Capgemini Power BI Interview: Multi-Layer RLS Implementation Guide

by

Capgemini: How Do You Implement Multi-Layer Row-Level Security (RLS) Across Regions, Departments & User Roles in Power BI?

This is one of the most frequently asked questions in Capgemini Power BI, Data Analyst, BI Developer, and Data Engineering interviews. Capgemini works with complex global clients who need dashboards where each user should only see the data they have permission to access. These permissions often depend on multiple attributes:

  • Region
  • Country
  • Department
  • Business Unit
  • Employee Role / Designation
  • Hierarchy Levels (Manager → Team Lead → Analyst)

Implementing multi-layer RLS that respects all these dimensions is one of the most complex Power BI tasks — and something Capgemini evaluates heavily during interviews.

Related: To master modeling before applying RLS, read our Infosys guide on troubleshooting relationship direction issues in Power BI .

For Microsoft’s official security concepts, explore Power BI Security Best Practices .

1. Why Capgemini Focuses Deeply on Multi-Layer RLS

Capgemini’s enterprise clients operate in:

  • BFSI (Banking & Insurance)
  • Healthcare
  • Retail
  • Telecommunications
  • Government & Public Sector
  • Manufacturing

In all these industries, data must be controlled with extreme precision. For example:

  • Managers can see data of their teams
  • Country heads can see data only for their country
  • Finance department sees only financial metrics
  • Marketing sees only campaign data
  • Executives may have full access

This requires multi-layered, dynamic RLS.

2. Types of RLS Used in Capgemini Projects

2.1 Static RLS

Hard-coded roles in Power BI Desktop. Not recommended for enterprises.

2.2 Dynamic RLS

Permissions stored in a security table, evaluated automatically.

2.3 Hierarchy-Based RLS

Managers see data of subordinates (recursive relationships).

2.4 Multi-Layer RLS

A combination of region-based, department-based, role-based, and hierarchy-based RLS.

3. Designing the Security Model (Capgemini-Style Architecture)

The secret to multi-layer RLS is a clean, centralized Security Table.

3.1 Example Security Table Structure


CREATE TABLE SecurityAccess (
    UserEmail VARCHAR(200),
    Region VARCHAR(100),
    Country VARCHAR(100),
    Department VARCHAR(100),
    RoleLevel VARCHAR(100),
    EmployeeID INT,
    ManagerID INT
);

3.2 Key Columns Explained

  • UserEmail: Used to map the logged-in user
  • Region/Country: For geography-based restriction
  • Department: For department-level access
  • RoleLevel: Analyst → Manager → Director → Executive
  • EmployeeID / ManagerID: Used for hierarchy filtering

3.3 Security Table Relationships

In the Power BI Model View, SecurityAccess must connect to:

  • DimRegion
  • DimCountry
  • DimDepartment
  • DimEmployee
  • FactSales / FactHR / FactFinance / etc.

4. Writing the RLS Expression

Capgemini expects the candidate to write DAX like this:


[User Access Filter] =
SecurityAccess[UserEmail] = USERPRINCIPALNAME()

4.1 Region-Level RLS


SecurityAccess[Region] = DimRegion[Region]

4.2 Department-Level RLS


SecurityAccess[Department] = DimDepartment[Department]

4.3 Hierarchy-Based RLS (Employee → Manager)


PATHCONTAINS(
  DimEmployee[ManagerPath],
  SecurityAccess[EmployeeID]
)

5. Complete Multi-Layer RLS Filter (Capgemini Interview-Level Answer)


FILTER(
  SecurityAccess,
  SecurityAccess[UserEmail] = USERPRINCIPALNAME()
)
&&
FILTER(
  DimRegion,
  DimRegion[Region] = SecurityAccess[Region]
)
&&
FILTER(
  DimDepartment,
  DimDepartment[Department] = SecurityAccess[Department]
)
&&
(
  SecurityAccess[EmployeeID] = DimEmployee[EmployeeID]
  ||
  PATHCONTAINS(DimEmployee[ManagerPath], SecurityAccess[EmployeeID])
)

6. Multi-Layer RLS Best Practices for Capgemini

  • Always use a single consolidated Security Table
  • Never apply RLS directly on fact tables
  • Avoid bi-directional relationships in RLS models
  • Test every user profile using “View As Role”
  • Store user emails in lowercase to avoid mismatch
  • Use import mode for the security table to avoid latency
  • Enable dynamic RLS instead of static roles

7. Capgemini-Style Interview Short Answer

“To implement multi-layer RLS in Power BI, I create a unified SecurityAccess table containing user email, region, country, department, role level, and hierarchy attributes. I link this table to the respective dimension tables (Region, Department, Employee). I then write dynamic RLS using USERPRINCIPALNAME() and apply filters across layers such as region-based, department-based, and employee hierarchy-based access. This ensures scalable, maintainable, multi-dimensional security — exactly what large Capgemini clients need.”

8. Conclusion

Multi-layer RLS is one of the most advanced Power BI concepts, and Capgemini expects candidates to master it. By using a centralized Security Table, clear relationships, and dynamic DAX filters, you can build robust RLS systems for global enterprise clients.