Power BI Row Level Security for Buyer and Factory Dashboards

A production dashboard can be correct and still reveal too much. The wrong person may see data they should not have. One buyer may be allowed to see its work at two plants. A factory manager may be allowed to see several buyers at one plant. A regional planner may have a more selective list. A page filter cannot express all those rights safely.

Power BI row level security, or RLS, controls which rows a report user can query. Start with an approved access rule. Build a model that preserves that rule. Microsoft describes RLS as role-based row filtering in a semantic model. Workspace permissions also affect whether it applies. Microsoft RLS documentation

This guide develops an original example for an imported apparel production model. It covers factory and buyer pairs, a small dynamic filter, and access tests. It also explains what to check when a user's job changes. The numbers are hypothetical. The model pattern needs testing in the actual tenant before release.

Turn the access request into a precise rule

Start with a sentence that names the user, permitted records, and intended use. “The buyer can view the dashboard” is incomplete. What else may the buyer see? The rule says nothing about other customers, costs, old orders, or subcontract sites.

A clearer rule is: “This account can view production records for Buyer North at Factory One and Buyer South at Factory Two.” That statement defines two permitted combinations. It does not grant every buyer at both factories.

Record the business owner who approved the scope. Give the grant a start date and, where needed, an end date. Keep a reason for temporary access, such as cover during leave. These are proposed operating controls. The company must decide who can approve them.

Separate viewing rights from development rights. A merchandiser may need a new chart. That does not mean they need to edit the shared model. Send the chart request to the report owner. Grant editing rights only through the team's approved process.

A report should support clear decisions. ApparelDiary's guide to factory KPI dashboard design helps define them. Security adds a further question: which source records may each decision maker use?

Preserve factory and buyer combinations

Consider a small production dataset. Each scope key represents one permitted combination of factory and buyer.

The example account has access to scope 101 and scope 202. Its correct total is:

Authorised pieces = 1,200 pieces + 900 pieces = 2,100 pieces.

Now suppose the model uses two separate lists. One grants both factories. The other grants both buyers. Every row now matches an allowed factory and an allowed buyer. That mistaken rule reveals all 3,300 pieces.

Unauthorised quantity exposed = 3,300 pieces − 2,100 pieces = 1,200 pieces.

The error is logical. Each list looks reasonable on its own. Their combination grants more than the approved request. Retaining a key for each factory–buyer pair avoids that accidental expansion.

Do not solve the problem by hard-coding these four keys into every report page. Store the approved pairs in an grant table and let the model enforce them. Add other scope fields only when the access rule needs them. For example, a supplier may require order-level rights rather than buyer-level rights.

Build a small model with explicit keys

For the worked design, use three tables. DimScope holds one row for each factory–buyer pair. FactProduction holds production transactions and their scope keys. UserScope holds approved account-to-scope assignments. These assignments are access grants, also called entitlements.

Relate DimScope to FactProduction using ScopeKey. DimScope is the one side; FactProduction is the many side. Use an active relationship with filtering from the scope dimension to the fact table.

Keep UserScope disconnected in this small reference pattern. The security expression will check it directly. The permission pair is then easy to inspect. This pattern does not need a bidirectional bridge. This is a deliberate choice for the example. Disconnected grants are not the best choice for every large model.

Microsoft explains fact and dimension roles in its star schema guidance. It also covers consistent grain and unique relationship keys. Apply those principles before adding the filter.

Do not join by a display label such as “North.” Labels may change or repeat. Maintain stable source codes and a controlled scope key. Keep the readable labels for users.

Match the account identity used at connection time

The grant table needs the identity that Power BI evaluates. The DAX function USERPRINCIPALNAME returns the user principal name at connection time. It has no input parameters. USERPRINCIPALNAME reference

For the example, the approved rows are:

These are fictional addresses. Use verified identities from the company's identity process in production.

An email alias and a sign-in identity may differ. External guest identities can also appear in different formats. Check the returned value using a restricted test report and the actual account. Do not guess the format from the address printed on a business card.

A user without a matching active grant should receive no authorised production rows. That outcome is useful evidence during testing. Review the missing access through the approved process. Do not grant all factories to every unknown user.

Define one dynamic role for this example

Create a role named ProductionScope in the imported model. Apply this filter to DimScope. The variable names are local to the expression.

VAR CurrentUser = USERPRINCIPALNAME()
VAR CurrentScope = DimScope[ScopeKey]
RETURN
COUNTROWS(
FILTER(
UserScope,
UserScope[UPN] = CurrentUser
&& UserScope[ScopeKey] = CurrentScope
&& UserScope[IsActive] = TRUE()
)
) > 0

The expression checks each scope row. Does an active grant match the connected account and that exact scope key? A positive count retains the row. With no match, the row does not pass.

Also apply a filter to UserScope in the same role:

UserScope[UPN] = USERPRINCIPALNAME()
&& UserScope[IsActive] = TRUE()

This second filter limits the grant rows visible to the account. Hiding the table in the report field list alone is not the intended protection.

The example uses COUNTROWS inside an RLS rule and therefore targets Import mode. Microsoft states that COUNTROWS is not supported in DirectQuery calculated columns or RLS rules. Do not copy this pattern into a DirectQuery model and assume equivalent support. COUNTROWS reference

For a larger implementation, compare this approach with a relationship-based grant design. Microsoft generally recommends dimension filters and well-designed active relationships. It also warns that RLS adds query work. The chosen design must preserve the same permission pairs. RLS design guidance

Trace every fact table through the security path

A production model may also contain shipments, order targets, inspections, and subcontract receipts. Securing FactProduction does not prove that the other tables are protected.

List the tables. Trace the active path from the secured scope dimension to each sensitive fact table. A shipment table may lack a valid scope key. Its total could stay visible while the production chart filters correctly.

Decide how to handle records with an unknown buyer or factory. For this design, hold those rows out of consumer release until their identity is resolved. Report their count to authorised support staff so they do not vanish from operational control.

Do not assign an unknown record to a convenient valid buyer merely to complete the refresh. That creates both an access error and an operational error.

Use one deliberately distinctive test value in each fact table. A quantity such as 701 pieces is easier to recognise during a test than several identical totals. Keep synthetic test rows outside live business reporting.

Separate model roles from workspace roles

Model roles decide the RLS filter. Workspace roles decide broader capabilities. They are different controls.

Microsoft states that RLS applies to workspace Viewers. Admin, Member, and Contributor roles have editing rights. They bypass this row restriction. Some consumers must remain subject to RLS. Do not give them an elevated workspace role just to fix a report-access problem. Workspace roles

Check direct membership and group membership. A person may appear to be a Viewer in one list while gaining a higher role through a group. Review the effective access route, not just the most convenient screen.

After publication, assign the intended consumers to the model role. Link the business approval to the identity in a controlled record. Include the workspace access route and grant rows.

Treat development accounts separately from acceptance-test accounts. An administrator's ability to open a report is not proof that the buyer's restrictions work.

Avoid accidental access through multiple roles

A narrow role does not cancel a broader one. Microsoft explains what happens when a user belongs to several roles. Access can include the union of their permitted rows. Multiple-role behaviour

In the worked model, use one dynamic consumer role with explicit grants. This keeps the account's intended scope in one place.

Consider a temporary “All Factories” role used during development. A buyer account might remain in that broad role after testing. Its narrow ProductionScope membership will not restore the intended restriction. Remove development grants before releasing the account.

Keep role combinations in the test plan even if policy says users should have only one role. The test should detect a configuration error that policy alone cannot prevent.

When an access exception is needed, add the approved scope through the normal grant process. Give it an expiry and review it. Avoid creating another broad role for every urgent request.

Test permitted and forbidden results

Begin with the four-scope dataset. Check both the exact allowed rows and the total. A correct grand total can hide a wrong mix of rows if quantities happen to match.

Record the role and identity for each test. Add the model version, grant version, and observed result. Include a forbidden buyer in a detail table. Confirm that the user cannot retrieve it through another page or permitted analysis route.

Use Desktop role testing during development. Repeat the checks in the service with real consumer accounts. Microsoft's service guidance describes Test as role. It also lists limits. These include external identity differences and some DirectQuery configurations. Service validation

Test the report pages that people will actually use. Include drillthrough, tooltips, totals, and another report connected to the same model. These checks concern the design's full use, not just its opening chart.

Test exports and new analyses deliberately

A user may need to export permitted order data or analyse it in Excel. Decide whether those capabilities fit the approved use, then test them with the restricted account.

Microsoft distinguishes Build permission from an elevated workspace role. A Viewer can receive Build permission for supported analysis. RLS still applies to that Viewer. Do not grant Contributor merely to provide that capability. RLS and Viewer permissions

For the example buyer, check the exported details. The allowed set contains only scopes 101 and 202. Compare unique scope keys and piece totals with the expected result.

Also review the information in filenames, bookmarks, titles, and manually typed text. A page title that names another buyer is outside the fact-table filter. Use neutral labels where a shared report serves different audiences.

No export control can recall a file that an authorised person already downloaded. The company's sharing rules must address that later use. This article covers access through the governed model and its supported paths. It cannot govern every later use of a file.

Protect sensitive columns with the right design

A permitted production row may contain a factory price, worker identifier, or margin. Row access does not by itself decide whether every column should be available.

Microsoft provides object-level security for restricting specified tables, columns, and their metadata. Consider that feature when audiences need different fields. Another option is a separate model that contains only the intended data. Object-level security

For a buyer progress report, a simpler design may omit internal cost fields from the consumer model entirely. That choice also makes the report easier to review.

Document whether a summary total itself is confidential. Two totals may reveal more than intended. Subtracting a restricted buyer total from an all-buyer total reveals the remainder. Decide the allowed comparison before building it.

Do not use a blank-returning measure as the only barrier around a sensitive base column. The access design must govern the data that an authorised query can request.

Plan changes and revocation around the refresh

In an imported model, the grant data is a loaded snapshot. Changing the source table does not instantly replace that snapshot.

Suppose an account loses scope 202 at 10:00, but the grant refresh finishes at 10:30. The team must understand the possible interval before the model reflects that change. The business may need faster revocation. Use an approved route to remove access, then verify its effect. Do not promise immediate revocation solely from a spreadsheet edit.

Keep operational timing measures separate:

Grant load delay = successful load completion time − approved source change time.

For the hypothetical change, the delay is 30 minutes. This measures the loading process; it does not prove when every access session ceased to work.

Define who monitors failed grant loads. Production facts may refresh while the access mapping stays stale. The model could then show new records under old rights. The release process should flag that mismatch. Stop unsafe distribution where needed.

Retain the previous mapping as audit evidence. Control access to that history. Do not overwrite it without a trace.

Measure performance without weakening the rule

Use a representative account with a small scope and one with a broad approved scope. Run the same pages and filters for each. Record query duration and identify the visuals that become slow.

Compare performance with and without RLS. Microsoft recommends Performance Analyzer and role testing for this work. RLS performance guidance

Check for duplicated grants in the factory example. Look for an oversized scope table or needless model paths. Review measures that scan more data than needed. Optimise the design while keeping the expected result set fixed.

A faster query is not an improvement if it changes 2,100 authorised pieces into 3,300 visible pieces. Keep the access test dataset available during tuning.

Include the cost of maintaining the model. The next owner needs to understand the design. A model that only one developer can explain is hard to change safely. Clear keys, a short grant rule, and a repeatable test pack help the next owner.

Make access support a routine process

Support staff need a decision path for “I see no data.” First confirm the intended account. Check the approved scope and active grant next. Review the last successful load, role membership, and relationship path.

Do not start by expanding access. An empty report may be the correct result for an expired assignment or a selected date with no work.

For “I see another buyer,” treat the issue as an access fault. Preserve the account, page, time, filter state, and visible scope keys. Restrict the affected distribution route while the authorised team investigates. The exact response should follow the organisation's process.

After the correction, rerun the positive and negative tests. Check whether a shared group, model deployment, or grant update affected other users.

Review temporary grants and inactive accounts on an agreed schedule. Confirm who still needs each scope. Resolve the exceptions and keep the approved list.

Decide what a transfer means for historical orders

A factory manager may move from one plant to another. The access request must say whether the person keeps access to the old plant's history. That is a business decision, not a default that the model developer should invent.

Consider a manager who transfers on 1 November. One policy might allow all history for the manager's current plant. It would remove the former plant entirely. Another might permit the former plant only through 31 October. Those rules need different data.

The first can be represented by replacing the active scope list. The second needs a time-bounded grant. Its filter must check each record date against the approved interval. The simple expression in this article does not implement that second rule. Adding dates to the table is not enough. An unchanged expression will not enforce a date limit.

Write separate acceptance cases before developing the extension. Use a record just before the change, one exactly at the boundary, and one just after it. State whether the boundary follows production date, order ownership date, or another event. Test a late correction to an older transaction as well.

A scope key should also have a stable meaning. A factory code might be reused for a different legal or operating unit. Old records must not silently inherit the new unit's rights. Keep identity changes explicit in the master data and in the access decision.

Deploy the rule and its evidence together

Treat a model release as more than a file upload. A change to a relationship can alter the security path even when the DAX role text stays the same. A new fact table can introduce another route to data.

Keep a short release record. Include the model version, role expressions, and relationship map. Add the grant source and approved test results. Name the person who reviewed the business scope. Also name the person who checked the implementation.

Use a staging model with synthetic or properly authorised test data. After publication to the target workspace, repeat the restricted-account checks. Confirm the actual role memberships there. Do not assume that a role with the same name has the same members in each environment.

For the worked example, retain the four-scope test table and its expected 2,100-piece result. Add tests when the model gains a shipment table or a date-bounded grant. The test pack should grow with the access rules rather than only with the number of report pages.

A deployment may fail a negative-access test. Follow the team's release process to restore the last approved distribution state or suspend the affected view. Record the correction and retest before reopening access.

Questions to settle before release

Can one account see two factories but different buyers in each?

Yes, if the access model preserves the permitted combinations. The worked scope-key design does that. Independent factory and buyer lists can grant an unintended cross-product.

Can a slicer replace row level security?

A slicer is part of report interaction. This design relies on the semantic model's security rule to govern allowed records. The slicer only helps users navigate within that permitted view.

Should the developer test with an administrator account?

Use the developer account for building, then use restricted consumer identities for acceptance. Elevated workspace rights can make a security test misleading.

Does a correct total prove the rule works?

No. Verify the permitted keys as well as the total. Also test forbidden rows, role combinations, exports, and account changes.

Release the permissions with the report

The business should be able to read the dashboard's access rule. The model must enforce that same rule. Keep factory–buyer pairs intact and use verified identities. Trace the intended filter path to every sensitive fact table.

The release evidence should show both what each test account can see and what it cannot see. Preserve that evidence when the model or access list changes. The team can then check both parts of the report: its numbers and who can see them.

Row access follows the same control principles as other systems, so see access control and visitor management, ERP for the apparel industry, the manufacturing analytics stack and factory payment controls.

References

• Microsoft Learn — Row-level security with Power BI.

• Microsoft Learn — Row-level security design guidance.

• Microsoft Learn — Roles in Power BI workspaces.

• Microsoft Learn — USERPRINCIPALNAME.

• Microsoft Learn — COUNTROWS.

• Microsoft Learn — Object-level security.

• Microsoft Learn — Star schema guidance.

• ApparelDiary — Factory KPI Dashboard Design.