Row-Level Security in Microsoft Fabric IQ - Planning Notes for Australian Data Teams

Row-level security is the part of a data platform project that gets thought about three days before go-live, and it is the part that breaks the go-live more than anything else. I have watched it happen at six different clients over the last decade. Everything is built. The reports look beautiful. Then somebody asks the question nobody asked at the start: who is allowed to see which rows, and how do we prove it.

With Microsoft Fabric IQ, the planning side of row-level security has improved a lot. Microsoft has put real thought into how you describe and enforce row-level rules at the semantic model layer, and the documentation around planning is genuinely useful. But the surface has also got bigger. RLS now spans Direct Lake models, semantic models, lakehouse SQL endpoints, and the agents and Copilot experiences built on top. The planning question is no longer just "which rows in the sales table".

I want to walk through how I think about row-level security planning on Fabric IQ projects right now, what works well, and the things to be careful about before you commit to a design.

What row-level security in Fabric IQ actually is

Row-level security in the Fabric IQ context is the set of rules that filter rows based on who is asking. A salesperson sees their own accounts. A regional manager sees their region. The CFO sees everything. The rules are defined at the semantic model layer, usually using DAX filter expressions tied to security roles, and they apply consistently across reports, paginated reports, Excel connections, and the AI surfaces like Copilot and Fabric Data Agents that read from the same model.

The Fabric IQ planning documentation, which is genuinely worth reading in full, frames the choice as a decision between dynamic and static RLS. Static RLS is when you have a small fixed set of roles, like "Sales", "Finance" and "Executive", and a user gets mapped to one. Dynamic RLS is when the filter is calculated at query time using the user's identity, usually their email or user principal name, joined to a security table that holds the mapping between users and the data they can see.

Static RLS is easier to build and easier to debug. Dynamic RLS is what nearly every real Australian business actually needs once you scratch the surface, because the org chart never fits into three neat buckets.

Where the planning actually goes wrong

The mistakes I see consistently are about scope and about ownership.

Scope creep happens when somebody writes down "the sales table is filtered by region" and stops there. Then six months later somebody asks the inevitable question. What about the customer table, since customers can be assigned to multiple reps. What about the product table, since some product lines are confidential to certain teams. What about the budget table, since regional budgets should only be visible to that region's leadership. Each of those is a different RLS pattern, and the cumulative complexity creeps up quietly. You end up with a semantic model that has fifteen RLS roles, each with three or four filters, and nobody can confidently say what a given user can or cannot see.

Ownership is the other one. RLS rules feel like they live in the data team, because they are written in DAX and the data team understands DAX. But the source of truth for who should see what is almost always sitting with the business. Sales ops owns the rep-to-account mapping. Finance owns the cost centre mapping. HR owns the org structure. If the RLS rules are written by the data team based on a snapshot of those mappings, they go stale within a quarter and stay stale until somebody complains. The right answer is to feed RLS off a live security table that the business owners can update through whatever system they already use.

We rebuilt a Fabric model for a logistics client late last year where the previous team had hard-coded the RLS into the DAX, listing depot codes one at a time inside the role definitions. There were forty seven depots. Whenever a depot was added or restructured, somebody had to open the model in Tableau Editor, edit DAX, and republish. They had been doing this for two years. Within six weeks of doing the rebuild on a security mapping table approach, depot changes became a SharePoint list edit that took thirty seconds.

What Fabric IQ does well for planning

The genuinely good news about Fabric IQ is that the planning surface has caught up with reality.

Direct Lake mode handles RLS sensibly. The filters apply at the model layer, the underlying delta tables in OneLake are not exposed directly to end users through the semantic model, and the lakehouse SQL endpoint can be configured with its own RLS that you can keep aligned with the semantic model. We have used this pattern on a couple of Microsoft Fabric projects this year and the consistency between the SQL endpoint and the report layer has held up well in audit.

The Fabric Data Agent layer respects RLS automatically when you ground it on a semantic model with roles defined. This is genuinely useful, and it is one of the few cases I can point to where the AI layer has been built with security baked in from the start rather than as an afterthought. If a user asks the data agent a question, the agent only sees rows the user has access to. The answer is filtered before the model ever sees it. That is the right architecture, and it is worth taking advantage of by funnelling agent traffic through models that already have RLS done properly rather than letting people stand up new agents on raw tables.

The planning documentation also calls out the testing problem explicitly, which I appreciate. You can test RLS as another user from within Power BI Desktop and now from the Fabric portal as well. This is the only way to actually verify the rules. Reading the DAX is not enough. You have to impersonate and check.

The traps to avoid

A few things that have bitten projects I have inherited.

Do not put performance-critical logic inside RLS expressions. The filter runs on every query, against every row, for every user. If your RLS expression includes a SUMX over a large fact table, you have just turned every report into a slow report. Keep the RLS expression as close to a join lookup as possible. Move any business logic into the security table itself rather than computing it in DAX.

Watch out for the empty result trap. A poorly written RLS rule can leave a user with no rows visible, but the report does not say "you have no access". It says "no data". The user thinks the system is broken. They lodge a ticket. The data team spends an hour figuring out it was RLS. Train report consumers to recognise the difference, or add explicit messaging to the report when zero rows are returned.

Be careful with composite roles. If a user is in two RLS roles, the filters are combined with OR logic, not AND. That sounds obvious until you hit a case where it is not what you wanted. A finance user who is also a regional manager should see all financial data for their region, not all financial data plus all regional data. Sometimes the OR is right. Sometimes it is not. You need to be explicit when you design the roles.

The most important trap is the gap between the semantic model and the underlying data source. RLS at the model layer does not stop a determined user from connecting to the underlying lakehouse SQL endpoint directly and pulling unfiltered data. If your data contains genuinely sensitive material like personal health information or banking credentials, model-layer RLS is necessary but not sufficient. You need the SQL endpoint and the OneLake delta tables locked down at their own permission layer too. This is something we talk about a lot on Power BI and broader Fabric engagements because most teams get this part wrong.

How I would plan an RLS rollout today

The pattern I have ended up recommending looks like this.

Map the security model on paper before anyone opens a Fabric workspace. Who needs to see what. Is the rule based on the user, the team, the org unit, or something computed from a transactional system. Get this signed off by whoever owns the data.

Build a single security mapping table in the lakehouse. One row per (user, dimension value) pair. Populate it from whatever the business already maintains. Refresh it on a schedule. This table is the heart of dynamic RLS and it is much easier to audit than DAX expressions.

Define RLS roles on the semantic model that filter through that mapping table. Keep the DAX simple. The complexity belongs in the mapping table, not the role definition.

Test by impersonation. Pick five real users from different parts of the org chart. Run the report as them. Confirm the rows match what they should see. Document this. Auditors will ask.

Plan for the AI layer. Fabric Data Agents that ground on RLS-protected semantic models will inherit the protection. Anything that grounds on raw tables will not. Steer agent development toward the protected model whenever possible.

Set up a regular review. Quarterly is probably right for most organisations. RLS rules go stale faster than you think, and a quarterly review catches the drift before it becomes a finding in an audit.

If you want a second pair of eyes on an RLS design for a Fabric project, the Microsoft Fabric consulting and Power BI consulting teams at Team 400 have done a lot of these. Industries with heavy data sensitivity, like financial services and healthcare, are where this work pays off the most, and they are also where the cost of getting it wrong is highest.

Row-level security is one of those topics that feels like a checklist item until you have actually shipped a few projects with it. The Fabric IQ approach is a real improvement on what we had before, but the planning still matters more than the tooling.

Original Microsoft documentation: Row-level security planning concept