參考答案
For a multi-region setup, I decide early whether the solution needs to scale. If there are only a few fixed regions and very few changes, static RLS works. But in most real scenarios, I implement dynamic RLS.
If I start with static RLS, I create one role per region, for example, North, South, East, and West. Inside each role, I define a DAX filter like:
[Region] = "North"
Then I publish the dataset and assign users to their respective roles in Power BI Service. This works, but it doesn't scale well. Every new region or manager means creating or modifying roles. That quickly becomes difficult to maintain.
For a scalable solution, I implement dynamic RLS.
I create a security mapping table with columns like UserEmail and Region. This table maps each user to the region they're allowed to see. Then I define a single role and write a DAX filter that references the logged-in user:
SecurityTable[UserEmail] = USERPRINCIPALNAME()
The Region column in that table connects to the Region column in the sales table through a relationship. Now, Power BI filters data dynamically based on who is logged in. If I need to onboard a new manager or change region access, I only add or update a row in the mapping table. I don't touch roles or the model structure.
Before deploying, I test thoroughly. In Power BI Desktop, I use "View As" to simulate different roles and confirm that the filtering behaves correctly. After publishing, I use "Test as role" in the Power BI Service to validate behavior under real user contexts.
If certain users should not see specific tables or columns at all, I implement Object Level Security (OLS). RLS filters rows, but OLS hides entire tables or fields. That's useful for restricting access to sensitive financial columns or internal calculations.
If the model contains many-to-many relationships, I validate that RLS propagates correctly. In some cases, I use CROSSFILTER inside measures to control filter direction explicitly. Incorrect relationship direction can either overexpose or overrestrict data.
I also verify how RLS interacts with aggregation tables. Totals and summary visuals must reflect only the permitted data. Aggregation tables should respect the same relationships so users never see numbers outside their region.
So my approach is: use static roles only when the structure is simple and stable. For anything dynamic or growing, implement a security mapping table with USERPRINCIPALNAME(), test thoroughly, and validate relationship behavior to ensure data isolation works correctly at scale.
加入Telegram學習組 ▷