Here's a scenario that plays out in organizations every day: your company's sales leadership wants a single Power BI dashboard that regional managers can access, but each manager should only see their own region's data. Your finance team wants a report that shows expense data filtered by cost center ownership. Your HR department needs a headcount analysis where department heads can see only their direct reports. The data itself lives in the same tables, the same warehouse, the same semantic model — but the view of that data must be radically different depending on who is looking at it.
Row-Level Security (RLS) is the mechanism that enforces this. And while Power BI's built-in RLS roles handle the enforcement layer at query time, what happens before that — how you shape, structure, and prepare the data in Power Query — determines whether your RLS implementation is robust, maintainable, and performant, or fragile, slow, and full of edge cases that will haunt you at 11pm before a board presentation.
This lesson is about the data preparation side of RLS. We're not going to spend much time on clicking buttons in the "Manage Roles" dialog. Instead, we're going to go deep on how to architect your Power Query transformations to support role-based access models properly: building user mapping tables, creating security bridge tables, handling multi-level hierarchies, managing dynamic versus static filtering approaches, and avoiding the performance and correctness traps that catch even experienced professionals. By the end of this lesson, you will be building the data foundation that makes RLS work reliably at scale.
What you'll learn:
Before working through this lesson, you should be comfortable with:
USERNAME(), USERPRINCIPALNAME(), and how they're used in RLS filter expressionsBefore we write a single line of M code, we need to be clear about what Power Query's job is in an RLS system versus what Power BI's role engine does. Conflating these responsibilities is the root cause of most RLS implementation failures.
Power BI's RLS engine applies filter context at query time. When a user with the "West Region" role opens a report, the engine intercepts the DAX query and injects a filter on the Region column. This happens transparently, after the dataset has been loaded. The engine is fast and reliable for simple, flat filtering scenarios.
Power Query's job is to ensure the data that gets loaded into the model has the right structure to support those filters. This includes:
If your data preparation is wrong, your RLS will appear to work in testing but fail in production — or worse, silently return wrong data to the wrong people.
The user mapping table is the cornerstone of any dynamic RLS implementation. Let's build one properly.
In most organizations, user-to-role mappings come from one of several places:
DimEmployee table that has a RegionCode foreign key)Each source has trade-offs. The warehouse dimension approach is the most maintainable because the mapping updates automatically when employee records change. The SharePoint list approach gives business owners control but requires governance. Manual Excel files are the fastest to set up and the first to rot.
For this lesson, we'll assume a hybrid: the primary mapping comes from a DimEmployee table in the warehouse, supplemented by an overrides table in SharePoint for contractors and service accounts that aren't in the HR system.
Here's a realistic Power Query M expression that builds a user mapping table by joining an employee dimension to a region hierarchy and normalizing the UPN format:
let
// Source: employee dimension from warehouse
Source = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT EmployeeID, Email, RegionCode, DepartmentCode, ManagerID, IsActive
FROM dw.DimEmployee
WHERE IsActive = 1"]),
// Normalize email to lowercase UPN format
// Azure AD is case-insensitive but DAX string comparisons are case-sensitive by default
NormalizeUPN = Table.TransformColumns(Source, {
{"Email", Text.Lower, type text}
}),
// Filter out service accounts (naming convention: svc-*)
ExcludeServiceAccounts = Table.SelectRows(NormalizeUPN,
each not Text.StartsWith([Email], "svc-")),
// Source: region lookup for human-readable region names
RegionLookup = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT RegionCode, RegionName, RegionGroupCode
FROM dw.DimRegion"]),
// Join to get region names (the RLS role filters on RegionName, not RegionCode)
JoinRegion = Table.NestedJoin(
ExcludeServiceAccounts, {"RegionCode"},
RegionLookup, {"RegionCode"},
"RegionDetail", JoinKind.Left
),
ExpandRegion = Table.ExpandTableColumn(JoinRegion, "RegionDetail",
{"RegionName", "RegionGroupCode"},
{"RegionName", "RegionGroupCode"}
),
// Source: SharePoint overrides for contractors/special accounts
SPOverrides = SharePoint.Tables("https://company.sharepoint.com/sites/DataGovernance",
[Implementation = "2.0"]){[Name = "RLS_UserOverrides"]}[Content],
SPCleaned = Table.SelectColumns(
Table.SelectRows(SPOverrides, each [IsActive] = true),
{"Email", "RegionCode", "RegionName", "RegionGroupCode"}
),
SPNormalized = Table.TransformColumns(SPCleaned, {
{"Email", Text.Lower, type text}
}),
// Add placeholder columns to match schema
SPWithIDs = Table.AddColumn(SPNormalized, "EmployeeID", each null, type text),
SPWithManager = Table.AddColumn(SPWithIDs, "ManagerID", each null, type text),
// Combine primary and override sources
// Override records take precedence — they appear first,
// and if there are duplicates, we'll handle them next
Combined = Table.Combine({SPNormalized,
Table.SelectColumns(ExcludeServiceAccounts,
{"Email", "RegionCode", "RegionName", "RegionGroupCode"})}),
// Deduplicate: keep the first occurrence (overrides win because they were appended first)
Deduplicated = Table.Distinct(Combined, {"Email"}),
// Final type enforcement
TypedTable = Table.TransformColumnTypes(Deduplicated, {
{"Email", type text},
{"RegionCode", type text},
{"RegionName", type text},
{"RegionGroupCode", type text}
})
in
TypedTable
Several design decisions here deserve explanation.
Why lowercase the UPN? DAX's USERPRINCIPALNAME() function returns whatever Azure AD has on file, which is typically lowercase. But your data warehouse might store emails in mixed case. A case mismatch means the filter returns zero rows — the user sees an empty report, not an error. Normalizing to lowercase in Power Query, and writing your DAX RLS filter as LOWER([Email]) = USERPRINCIPALNAME(), eliminates this class of bug entirely.
Why use Table.Distinct with overrides first? If a contractor is in both the SharePoint overrides list and the warehouse (unlikely but possible during a transition period), we want the explicitly managed override to win. By appending overrides before the warehouse data and then deduplicating on email, we achieve this without complex conditional logic.
Why filter IsActive = 1 in SQL rather than in Power Query? Query folding. When the WHERE clause is part of the native SQL query, the database engine handles the filtering before returning data across the network. If you filter in Power Query after pulling all rows, you're shipping dead records over the wire. For a small employee table this doesn't matter. For a 500,000-row employee table in a global enterprise, it absolutely does.
Warning: Every step after a
SharePoint.Tables()call breaks query folding for that branch of the query, because SharePoint is not a foldable source. Keep SharePoint data manipulations minimal, and never join non-foldable sources in a way that forces a large SQL source to also become non-foldable. UseTable.Buffer()strategically on small SharePoint results to prevent the M engine from repeatedly re-fetching them.
Simple RLS assumes one user maps to one filterable value — one region, one department, one cost center. Real organizations are messier. A regional VP might oversee multiple regions. A compliance officer might need read access to all departments. A matrix organization might have people reporting into multiple cost centers simultaneously.
When the relationship between users and access-granting entities is many-to-many, you need a security bridge table.
The bridge table has exactly two meaningful columns: the user identifier and the filterable dimension key. One user can appear on multiple rows (one per entity they can access), and one entity key can appear on multiple rows (one per user who can access it).
let
// We're building a security bridge for cost center access
// Source 1: Direct ownership — an employee owns their primary cost center
DirectOwnership = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT LOWER(e.Email) AS UserUPN,
cc.CostCenterCode,
'Direct' AS AccessType
FROM dw.DimEmployee e
JOIN dw.DimCostCenter cc ON e.CostCenterID = cc.CostCenterID
WHERE e.IsActive = 1"]),
// Source 2: Delegated access — finance BPs who have view access to other cost centers
DelegatedAccess = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT LOWER(u.Email) AS UserUPN,
da.CostCenterCode,
'Delegated' AS AccessType
FROM dw.UserDelegatedAccess da
JOIN dw.DimEmployee u ON da.UserID = u.EmployeeID
WHERE da.IsActive = 1 AND da.AccessType = 'CostCenter'"]),
// Source 3: Admin override — CFO and Controller see everything
// Rather than listing every cost center, we handle this differently in the role definition
// (they get a separate role with no filter), so we intentionally exclude them here
// Combine all access types
AllAccess = Table.Combine({DirectOwnership, DelegatedAccess}),
// Deduplicate: if someone has both direct and delegated access to the same cost center,
// we only need one row — the bridge just needs the relationship to exist
UniqueBridge = Table.Distinct(AllAccess, {"UserUPN", "CostCenterCode"}),
// Drop AccessType — it was useful for debugging but shouldn't be in the security table
// because it could leak information about why someone has access
FinalBridge = Table.SelectColumns(UniqueBridge, {"UserUPN", "CostCenterCode"}),
TypedFinal = Table.TransformColumnTypes(FinalBridge, {
{"UserUPN", type text},
{"CostCenterCode", type text}
})
in
TypedFinal
In your Power BI model, this bridge table sits between the DimEmployee_Security table (containing UPNs) and the DimCostCenter dimension. The RLS filter expression on the security table filters to the current user's UPN, and the relationship propagates through the bridge to filter the cost center dimension, which in turn filters all fact tables connected to it.
Tip: Name your security tables with a clear prefix like
Security_UserCostCenterorBridge_RLS_CostCenter. When someone opens the model six months from now, they'll immediately understand that these tables are infrastructure, not reporting data. This naming convention also makes it easy to exclude security tables from end-user view in the Field List.
One common mistake is building a bridge row for every cost center for admin users. If you have 3,000 cost centers and 12 admin users, that's 36,000 rows in your bridge table just for admins — and any time a new cost center is added, you have to remember to add 12 more rows.
The better approach: create a separate Power BI role with no DAX filter expression for admins. In Power Query, you don't need to represent their access at all. This is a model architecture decision that saves significant complexity in the data prep layer.
This is where things get genuinely interesting. An organizational hierarchy creates a natural security challenge: a manager should see their own data and all of their direct and indirect reports' data. This isn't a flat mapping problem — it's a graph traversal problem, and M handles it in ways that aren't immediately obvious.
Given an employee table with EmployeeID and ManagerID columns, we need to produce a mapping from every manager to every employee in their subtree. This is fundamentally a recursive operation.
M doesn't support direct recursion (a function calling itself by name), but it does support something functionally equivalent through List.Generate, which can iterate with state.
let
// Start with the raw employee hierarchy
Employees = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT EmployeeID, LOWER(Email) AS UserUPN, ManagerID, DepartmentCode
FROM dw.DimEmployee
WHERE IsActive = 1"]),
// Buffer the source — we'll reference it multiple times in the recursive expansion
BufferedEmployees = Table.Buffer(Employees),
// Build a lookup: ManagerID -> list of direct report EmployeeIDs
// This is our adjacency list representation of the hierarchy graph
DirectReportsList = Table.Group(
BufferedEmployees,
{"ManagerID"},
{{"DirectReports", each [EmployeeID], type list}}
),
// Convert to a record for O(1) lookup by ManagerID
DirectReportsRecord = Record.FromList(
DirectReportsList[DirectReports],
List.Transform(DirectReportsList[ManagerID], Text.From)
),
// For each manager, expand their subtree iteratively
// We use List.Generate to simulate BFS (breadth-first search)
GetSubtree = (startManagerID as text) as list =>
let
// List.Generate: initial state, condition, next state, selector
Result = List.Generate(
// Initial state: frontier = direct reports of the starting manager
() => [
Frontier = if Record.HasFields(DirectReportsRecord, startManagerID)
then Record.Field(DirectReportsRecord, startManagerID)
else {},
Visited = {startManagerID}
],
// Condition: keep going while there are nodes to explore
each List.Count([Frontier]) > 0,
// Next state: expand the frontier by one level
each [
Frontier = List.Difference(
List.Combine(
List.Transform(
[Frontier],
(empID) =>
if Record.HasFields(DirectReportsRecord, Text.From(empID))
then Record.Field(DirectReportsRecord, Text.From(empID))
else {}
)
),
[Visited] // Don't revisit nodes (handles cycles in bad data)
),
Visited = List.Combine({[Visited], [Frontier]})
],
// Selector: emit the current frontier at each step
each [Frontier]
),
// Flatten all frontier lists, add the starting manager themselves
AllDescendants = List.Combine(List.Combine({{startManagerID}, Result}))
in
AllDescendants,
// Get all unique manager UPNs
ManagerRows = Table.SelectRows(BufferedEmployees,
each List.Contains(BufferedEmployees[ManagerID], [EmployeeID])),
UniqueManagers = Table.Distinct(
Table.SelectColumns(ManagerRows, {"EmployeeID", "UserUPN"}),
{"EmployeeID"}
),
// For each manager, generate one row per employee in their subtree
ExpandedAccess = Table.AddColumn(UniqueManagers, "SubtreeEmployeeIDs",
each GetSubtree(Text.From([EmployeeID])),
type list),
// Explode the list into individual rows
ExplodedRows = Table.ExpandListColumn(ExpandedAccess, "SubtreeEmployeeIDs"),
// Rename and clean up
RenamedColumns = Table.RenameColumns(ExplodedRows, {
{"SubtreeEmployeeIDs", "AccessibleEmployeeID"}
}),
FinalHierarchyBridge = Table.SelectColumns(RenamedColumns, {
"UserUPN", "AccessibleEmployeeID"
}),
TypedFinal = Table.TransformColumnTypes(FinalHierarchyBridge, {
{"UserUPN", type text},
{"AccessibleEmployeeID", type text}
})
in
TypedFinal
Let's talk through what this is actually doing. The GetSubtree function performs a breadth-first traversal using List.Generate. We start with a manager's direct reports as the frontier, then in each iteration we expand the frontier by fetching the direct reports of every node in the current frontier. We accumulate visited nodes to handle two situations: normal termination (leaf nodes have no direct reports) and data quality issues (circular references in the hierarchy, which happen more often than you'd think in HR data).
The Record.FromList trick is critical for performance. If we used Table.SelectRows inside the traversal to find direct reports for each node, we'd be doing a full table scan for every node in the hierarchy. By converting the adjacency list to a record structure upfront, lookups are O(1) instead of O(n). On a hierarchy of 10,000 employees, this difference is the boundary between "runs in 30 seconds" and "times out."
Warning:
List.Generatewith complex state objects can be memory-intensive for very large hierarchies (50,000+ nodes). If you're working at that scale, consider pre-computing the hierarchy closure in your database using a recursive CTE and a materialized view, then loading the result directly into Power Query. Let the database engine do what it's designed for.
Real organizations often layer explicit access grants on top of the automatic hierarchy. Someone might manage a cross-functional team that doesn't sit in their org chart. Handle this by combining your hierarchy bridge with an explicit grants table:
let
HierarchyBridge = // ... the output of the query above ...,
ExplicitGrants = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT LOWER(u.Email) AS UserUPN,
ag.GrantedEmployeeID AS AccessibleEmployeeID
FROM dw.AccessGrants ag
JOIN dw.DimEmployee u ON ag.RequestorID = u.EmployeeID
WHERE ag.IsActive = 1 AND ag.GrantType = 'Employee'"]),
Combined = Table.Combine({HierarchyBridge, ExplicitGrants}),
Deduplicated = Table.Distinct(Combined, {"UserUPN", "AccessibleEmployeeID"})
in
Deduplicated
The deduplication here prevents duplicate rows when someone's explicit grant happens to overlap with an access they already have through the hierarchy.
This is a distinction that trips up a lot of practitioners, and it has significant implications for how you structure your Power Query work.
Static RLS means role membership is controlled by manually assigning users to roles in Power BI Service. The role definitions (DAX filter expressions) are fixed at development time. Data preparation is simpler because you're just ensuring the dimension values in your filter expression exist and are correctly typed.
Dynamic RLS means the role uses USERPRINCIPALNAME() or USERNAME() in the DAX filter to look up the current user in a mapping table at runtime. Data preparation is where all the real work happens — your user mapping table is the security policy.
For dynamic RLS, every deployment must ensure:
The mapping table is up to date. If an employee transfers regions and your dataset refreshes nightly, there's a window of up to 24 hours where they see the wrong data. For sensitive data, this is unacceptable — consider more frequent refreshes or incremental refresh strategies.
New users are handled gracefully. When someone is added to the system but hasn't yet appeared in your mapping table refresh, the RLS filter finds no matching row and returns empty data. You need a monitoring process that detects users who are authenticated but have zero matching rows in the security table.
The mapping table is excluded from visual use. Security tables should have all columns hidden or be entirely hidden from the report view. Users should not be able to browse or slice by the security table — this would expose the entire user mapping.
For datasets that serve multiple purposes (some reports are RLS-protected, others are for admins who bypass RLS), you can use Power Query parameters to switch security modes during development and testing:
// Parameter: SecurityMode (allowed values: "Dynamic", "Static", "Admin")
let
SecurityMode = SecurityModeParameter, // Defined as a Query Parameter in Power Query
BaseMapping = // ... your normal user mapping query ...,
FinalMapping = if SecurityMode = "Admin"
then Table.FirstN(BaseMapping, 0) // Empty table — admin role has no filter
else if SecurityMode = "Static"
then Table.SelectRows(BaseMapping, each [RoleGroup] = StaticRoleParameter)
else BaseMapping // Dynamic mode: full mapping table
in
FinalMapping
Tip: Never commit an "Admin" or "Static" mode to your production dataset. These parameters are for local development and testing only. In production, the dataset should always be in "Dynamic" mode with the full mapping table loaded. Consider adding a deployment checklist item that verifies parameter values before publishing.
Security tables have characteristics that differ from typical reporting tables, and those differences create specific optimization opportunities and traps.
Every row in your user mapping table exists to support one filter operation at query time. Columns beyond the UPN and the filterable dimension keys are waste. Strip them out in Power Query before loading. A security table should have 2-4 columns maximum.
The one exception: a LastUpdated timestamp column that you add to track data freshness. This helps with debugging and monitoring without adding significant size.
If your security table ends up in the VertiPaq column store with millions of rows (because you accidentally loaded a full hierarchy bridge for a large organization), it will consume memory and slow down all model queries — not just RLS-filtered ones. Profile your table sizes using DAX Studio's VertiPaq Analyzer before and after loading security tables.
Security table refreshes fail in ways that are worse than normal table refresh failures. If your sales fact table fails to refresh, users see stale data. If your user mapping table fails to refresh, users might see no data (if the refresh partially completes) or the wrong data (if the failure leaves a corrupted table state).
Implement table-level refresh ordering in your dataset: always refresh security tables first, verify they loaded correctly (row count > 0 is a bare minimum check), then refresh fact tables. This sequencing is configured in the dataset refresh settings in Power BI Service for Premium capacity datasets.
Because security tables often combine multiple sources (warehouse + SharePoint, or warehouse + flat file), they frequently break query folding. This is usually acceptable — security tables are small and refresh infrequently. But you should be explicit about where folding breaks:
let
// This folds to the database — good
WarehouseData = Sql.Database(..., [Query = "SELECT ..."]),
// Adding a custom column breaks folding from this point forward
// But since our next steps are just Table.SelectColumns and Table.Distinct,
// the performance impact is minimal for a ~500 row security table
WithDerivedColumn = Table.AddColumn(WarehouseData, "Domain",
each Text.BetweenDelimiters([UserUPN], "@", ".")),
Final = Table.Distinct(
Table.SelectColumns(WithDerivedColumn, {"UserUPN", "RegionCode", "Domain"}),
{"UserUPN"}
)
in
Final
You can verify query folding status in Power Query Editor by right-clicking a step and checking whether "View Native Query" is available (available = still folding, grayed out = folding has broken).
Getting the data structure right in Power Query is only half the battle. You need a systematic way to verify that the security fabric you've built actually enforces the intended access patterns.
Power BI Desktop's "View As Roles" feature (accessible via the Modeling tab) lets you preview a report as a specific role. This is useful for smoke testing but insufficient for systematic verification because:
USERPRINCIPALNAME() returns your own UPN in this mode, not the test user's UPNCreate a dedicated "Test_RLSCoverage" query (not loaded to the model, marked as a staging query) that validates your security table completeness:
let
// Load the security bridge
SecurityBridge = UserCostCenterBridge,
// Load all active cost centers
AllCostCenters = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT CostCenterCode FROM dw.DimCostCenter WHERE IsActive = 1"]),
// Load all active users who should have access
AllUsers = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT LOWER(Email) AS UserUPN FROM dw.DimEmployee WHERE IsActive = 1"]),
// Find cost centers that appear in no security row — these are orphaned
OrphanedCostCenters = Table.SelectRows(
AllCostCenters,
each not List.Contains(SecurityBridge[CostCenterCode], [CostCenterCode])
),
// Find users with no security rows — these users will see empty reports
UsersWithNoAccess = Table.SelectRows(
AllUsers,
each not List.Contains(SecurityBridge[UserUPN], [UserUPN])
),
// Summary diagnostic record
DiagnosticSummary = [
TotalBridgeRows = Table.RowCount(SecurityBridge),
OrphanedCostCenterCount = Table.RowCount(OrphanedCostCenters),
UsersWithNoAccessCount = Table.RowCount(UsersWithNoAccess),
OrphanedCostCenterList = Text.Combine(OrphanedCostCenters[CostCenterCode], ", "),
GeneratedAt = DateTime.LocalNow()
]
in
DiagnosticSummary
This query can be loaded into a separate diagnostic report or exported as a JSON record for monitoring. Set up an alert on OrphanedCostCenterCount > 0 or UsersWithNoAccessCount > 0 to catch security gaps automatically after each refresh.
You're the data engineer for a mid-size retail company. The VP of Retail Operations wants a Power BI report showing store performance metrics. Access rules are:
Your source data is a SQL table dw.StoreHierarchy with columns: StoreID, StoreName, StoreManagerEmail, DistrictID, DistrictName, DistrictManagerEmail, RegionID, RegionName, RegionalVPEmail.
Your task:
Step 1: Write a Power Query M expression that produces a Security_StoreAccess bridge table with two columns: UserUPN and StoreID. The table should capture all three tiers of the hierarchy — store managers see their store, district managers see all stores in their district, regional VPs see all stores in their region.
Step 2: The CRO wants to be added without being listed in any security rows. Describe (in 2-3 sentences) how you'd handle the CRO's access in the Power BI role model rather than in Power Query.
Step 3: After loading your security table, you discover that 3 district managers appear twice in the source data (duplicate rows with slightly different email casing). Write the Power Query step that resolves this without losing legitimate access rows.
Worked Solution:
// Step 1: Build the three-tier security bridge
let
Source = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT StoreID,
LOWER(StoreManagerEmail) AS UserUPN
FROM dw.StoreHierarchy"]),
// Store managers: direct 1:1 mapping
StoreManagerRows = Table.SelectColumns(Source, {"UserUPN", "StoreID"}),
// District managers: one DM email maps to all stores in their district
DistrictSource = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT LOWER(DistrictManagerEmail) AS UserUPN, StoreID
FROM dw.StoreHierarchy"]),
DistrictManagerRows = Table.SelectColumns(DistrictSource, {"UserUPN", "StoreID"}),
// Regional VPs: one VP email maps to all stores in their region
RegionSource = Sql.Database("dw-prod.company.com", "Analytics",
[Query = "SELECT LOWER(RegionalVPEmail) AS UserUPN, StoreID
FROM dw.StoreHierarchy"]),
RegionalVPRows = Table.SelectColumns(RegionSource, {"UserUPN", "StoreID"}),
// Combine all three tiers
Combined = Table.Combine({StoreManagerRows, DistrictManagerRows, RegionalVPRows}),
// Step 3: Resolve duplicate emails from case variations before deduplicating
// Normalize UPN again (in case the SQL LOWER() was inconsistently applied)
NormalizedCombined = Table.TransformColumns(Combined, {
{"UserUPN", Text.Lower, type text}
}),
// Remove duplicate (UserUPN, StoreID) pairs — handles both the case-variation duplicates
// and the natural overlap where a DM's own store appears in both their store-manager row
// and their district-manager rows
Deduplicated = Table.Distinct(NormalizedCombined, {"UserUPN", "StoreID"}),
TypedFinal = Table.TransformColumnTypes(Deduplicated, {
{"UserUPN", type text},
{"StoreID", Int64.Type}
})
in
TypedFinal
Step 2 answer: Create a separate Power BI role called "CRO" with an empty DAX filter expression (leave the table filter completely blank, or set it to a tautology like [StoreID] = [StoreID]). Assign the CRO's Azure AD account directly to this role in Power BI Service. This role bypasses the security bridge entirely, meaning no Power Query changes are needed when the CRO role changes hands — only the role membership assignment in the Service changes.
Step 3 answer: The NormalizedCombined step handles the root cause (case variation) and Table.Distinct on the {"UserUPN", "StoreID"} pair handles the symptom. Since we normalize first, dm.jones@company.com and DM.Jones@Company.com both become dm.jones@company.com before deduplication, so no access rows are lost — we keep exactly one row per unique (lowercase UPN, store ID) combination.
What happens: You load the full user mapping table including EmployeeName, Department, JobTitle, and HireDate columns "for reference." These columns bloat the VertiPaq store and, worse, can be browsed by technically sophisticated users who know how to use DAX queries directly against the dataset.
Fix: Use Table.SelectColumns as the final step of every security table query. Load only what the RLS filter relationship requires.
What happens: Someone writes a DAX RLS filter like Text.Contains([UserUPN], USERNAME()) thinking this is safer or more flexible. It creates a catastrophic security hole — a user with UPN admin@company.com would match any UPN that contains "admin".
Fix: Always use exact match: [UserUPN] = LOWER(USERPRINCIPALNAME()). No partial matching, no wildcards.
What happens: You rely on Table.Combine ordering to ensure override rows appear before warehouse rows for the deduplication step. In some refresh scenarios, the order is non-deterministic.
Fix: Add an explicit priority column before combining, then sort on it before deduplicating:
SPWithPriority = Table.AddColumn(SPNormalized, "Priority", each 1, Int64.Type),
WarehouseWithPriority = Table.AddColumn(WarehouseData, "Priority", each 2, Int64.Type),
Combined = Table.Combine({SPWithPriority, WarehouseWithPriority}),
Sorted = Table.Sort(Combined, {"Priority", Order.Ascending}),
Deduped = Table.Distinct(Sorted, {"UserUPN"})
Now override rows (Priority 1) always win over warehouse rows (Priority 2), regardless of combine order.
What happens: Your HR data has a data quality issue where Employee A is listed as managing Employee B, and Employee B is listed as managing Employee A. Your List.Generate-based traversal enters an infinite loop and the refresh times out.
Fix: Track visited nodes and use List.Difference to prevent revisiting, exactly as shown in the GetSubtree function above. Additionally, add a data quality check upstream:
// Detect cycles: any employee who appears as their own ancestor is a cycle indicator
PotentialCycles = Table.SelectRows(Employees, each [EmployeeID] = [ManagerID])
Log these and alert the HR data team before they cause refresh failures.
What happens: A new employee starts on Monday, is provisioned in Azure AD immediately, but your nightly RLS mapping table refresh hasn't run yet. They open the report and see completely empty visuals with no error — an infuriatingly silent failure.
Fix: Add a "catch-all" row logic in your security table for users not found in the mapping. One approach: in your DAX role filter, handle the no-match case explicitly:
// Instead of this (returns nothing if user not in table)
[UserUPN] = USERPRINCIPALNAME()
// Use this (returns a specific "access denied" notice instead of silent empty)
[UserUPN] = USERPRINCIPALNAME() ||
USERPRINCIPALNAME() = "access-denied@placeholder.company.com"
Then add a dedicated "Access Denied" report page that displays when all other pages show no data. Better: set up a Power Automate flow that detects new Azure AD users and triggers a dataset refresh.
We've covered a lot of ground. Here's what you've built competence in:
User mapping tables are the foundation of dynamic RLS, and they require careful attention to UPN normalization, source priority, deduplication strategy, and service account exclusion. Getting this right prevents the silent-empty-report failures that are maddeningly hard to diagnose.
Security bridge tables solve the many-to-many access problem that flat mapping can't handle. Keep them lean — two columns, deduplicated, no extra context columns — and position them correctly in the model between the identity dimension and the filterable dimension.
Hierarchical access requires either database-side recursive CTEs (preferred for large organizations) or careful M-language BFS traversal using List.Generate with cycle detection. The Record.FromList adjacency lookup pattern is the key to making this performant.
Static vs. dynamic RLS is a data preparation philosophy choice. Dynamic RLS puts your Power Query work front and center as the security policy; static RLS makes Power BI Service role assignment the authority. Most enterprise implementations use dynamic RLS for regular users and a static "Admin" role for superusers.
Testing and monitoring are not afterthoughts. Your Test_RLSCoverage diagnostic query should run on every refresh and alert you to orphaned entities and users with no access mapping.
Implement object-level security (OLS) alongside RLS — OLS lets you hide entire tables and columns from specific roles, complementing the row-level filtering you've built here.
Explore Premium-per-User and Premium capacity RLS performance profiling using DAX Studio's Server Timing trace to measure the actual execution cost of your RLS filters in production.
Investigate composite models and DirectQuery RLS — when your fact table is in DirectQuery mode, RLS filters translate directly into WHERE clauses on the source database, which has different performance characteristics and requires different optimization strategies than import mode.
Build a governance process around your security tables: who has permission to add rows to the overrides table? What's the review process for delegated access grants? The data preparation is only as trustworthy as the governance around it.
Automate security table validation with a Python or Power Automate script that runs after each dataset refresh, checks the diagnostic summary query output, and sends alerts if coverage gaps are detected.
The line between a secure, trustworthy RLS implementation and a porous, unmaintainable one is almost always drawn in Power Query. The report layer is the window — the data preparation layer is the lock on the door.
Learning Path: Power Query Essentials