Row-Level Security in Power BI
Row-level security (RLS) is a filter on rows, by role, so one report serves many people. The Pune role sees Pune’s 40. The Mumbai role sees Mumbai’s 150. You test with View as before anyone else opens the report. This page is how to set that up. It is not a way around security.
Friends! Two city managers should not see each other’s sales. Why one report? Because two PBIX files drift apart. How? We put a filter on the Store table for each role, the filter walks into Sales, and we view as Pune before we assign a person in the Service. The right person, the right rows.
मित्रांनो! दोन city managers नी एकमेकांचे sales बघू नयेत. एक report का? कारण दोन PBIX फाइल वेगळ्या होतात. कसे? प्रत्येक role साठी Store टेबलवर फिल्टर, फिल्टर Sales मध्ये जातो, आणि Service मध्ये व्यक्ती जोडण्यापूर्वी View as Pune. योग्य व्यक्ती, योग्य rows.
मित्रों! दो city managers एक-दूसरे की sales न देखें. एक report क्यों? क्योंकि दो PBIX फ़ाइलें अलग हो जाती हैं. कैसे? हर role के लिए Store टेबल पर फ़िल्टर, फ़िल्टर Sales में जाता है, और Service में व्यक्ति जोड़ने से पहले View as Pune. सही व्यक्ति, सही rows.
Quick answer
Vertical path:
- Modeling → Manage roles.
- New role named Pune. On Store, City equals Pune.
- New role named Mumbai. On Store, City equals Mumbai.
- Modeling → View as → Pune. The card must read 40, not 190.
- Stop viewing. View as Mumbai. The card must read 150.
- After publish, semantic model → Security → add members to each role. Consumers must be Viewers, not workspace builders.
Role filter on Store[City] → Sales rows that match → the card
The rows each role keeps
Sales:
| Order | Store ID | Amount |
|---|---|---|
| A | S1 | 100 |
| B | S1 | 50 |
| C | S2 | 40 |
Store:
| Store ID | City |
|---|---|
| S1 | Mumbai |
| S2 | Pune |
- No role, you as the author in Desktop: 190.
- Role Pune: Store keeps S2. Sales keeps order C. Card 40.
- Role Mumbai: Store keeps S1. Sales keeps A and B. Card 150.
- The hidden rows are not on the visual, and they are not in the export for that role.
- A measure cannot be used as a back door to those rows. The role filter stays. Do not go looking for a trick. Set the role correctly instead.
Put the filter on the dimension (Store), not only on a visual. A hidden page is not security. Anyone who can build can see the fields.
What do I need before this guide?
- A relationship from Sales to Store (relationships).
- Consumers planned as Viewers (workspaces).
- Course: What RLS is · Static RLS · Testing.
Before and after (look at the tables first)
Before - Everyone sees all rows. Too many rows
आधी (Before) — सर्वांना सर्व rows. खूप rows.
पहले (Before) — सबको सभी rows. बहुत rows.
After - Role filter. Test with View as
नंतर (After) — Role फिल्टर. View as ने तपासा.
बाद में (After) — Role फ़िल्टर. View as से जाँचें.
How to create and test the roles
A role filters Store[City]. Pune keeps 40. Mumbai keeps 150. The filter walks into Sales.
Role Store[City] फिल्टर करते. Pune कडे 40. Mumbai कडे 150. फिल्टर Sales मध्ये जातो.
Role Store[City] फ़िल्टर करती है. Pune के पास 40. Mumbai के पास 150. फ़िल्टर Sales में जाता है.
- Modeling → Manage roles → New → name it Pune.
- Select the Store table. Add a filter: City is Pune. Or switch to the DAX editor and use
[City] = "Pune". - Save. Create Mumbai the same way with
"Mumbai". - Modeling → View as → tick Pune → OK. A bar says you are viewing as that role.
- Read the card. It must be 40. Open the table. Only Pune rows.
- Stop viewing. Repeat for Mumbai and 150.
- If a role sees 190, the filter is on the wrong table, the relationship is missing, or you are not really viewing as that role.
View as proves the role before you assign people. In the Service, add members under the semantic model Security page.
View as लोक जोडण्यापूर्वी role सिद्ध करते. Service मध्ये semantic model च्या Security पानावर members जोडा.
View as लोगों को जोड़ने से पहले role साबित करता है. Service में semantic model के Security पन्ने पर members जोड़ें.
In the Service, after you publish:
- Workspace → semantic model → Security.
- Select the Pune role. Add the Pune readers, or a security group.
- Add Mumbai readers to the Mumbai role only.
- Use Test as role. Check the card, not only the city name.
- Share through Viewer or an app. Do not add those readers as Admin, Member, or Contributor.
Builders see all rows. If you test with your own Admin account, you will see 190 and think the role failed. Test as the role, or with a Viewer account.
One role for many managers (dynamic)
Static roles are one filter value per role. When every manager has a sign-in and their email is stored on the Store row, one role can compare that column to the person who opened the report:
[Manager Email] = USERPRINCIPALNAME()
- USERPRINCIPALNAME returns the sign-in, usually an email-style name.
- Example:
manager.pune@example.comon the Pune store row. That person sees 40. - The spelling must match the sign-in exactly. An old address sees nothing. That is a data fix, not a reason to remove the role.
- View as → Other user, and type the example address, to test without borrowing anyone’s password.
- Hide the security table from report view if you add a mapping table. Readers do not need to browse the list of who sees what.
Object-level security is different: it hides a whole table or column from a role. This guide does not set that up. Row-level security is the row filter above.
If a person is in two roles, they see the rows of both. A person in Pune and Mumbai sees 190. Add people only to the role they should have.
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| View as still shows 190 | Filter on the wrong table, or no relationship | Filter Store[City]; check the line |
| Manager sees every city | They are Admin, Member, or Contributor | Give Viewer or app access |
| Manager sees nothing | Email does not match, or they were not added to the role | Fix the member list or the address |
| Blank city and a full total | Role not tested | View as before you assign people |
Ravindra Bagale's Tip
Interview line: “RLS filters rows by role so each person sees only their cities. I test with View as, I assign viewers in the Service, and I never treat a hidden page as security.” Got it?
Ravindra Bagale's Tip – मराठी
Interview line: “RLS role ने rows फिल्टर करते म्हणजे प्रत्येक व्यक्ती फक्त त्यांची cities बघते. मी View as ने तपासतो, Service मध्ये viewers जोडतो, आणि लपवलेले page कधी security मानत नाही.” समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview line: “RLS role से rows फ़िल्टर करता है ताकि हर व्यक्ति सिर्फ अपने cities देखे. मैं View as से जाँचता हूँ, Service में viewers जोड़ता हूँ, और छिपे page को कभी security नहीं मानता.” समझ में आया?
Practice task
- Create Pune and Mumbai roles on Store[City].
- View as Pune. Record the card (40).
- View as Mumbai. Record the card (150).
- Stop viewing and confirm you, the author, see 190 again.
- Write who will be a Viewer in the Service, and who must not be a Member.
Got it? One report, a role per audience, View as before you share. Pune sees 40. Mumbai sees 150. That is the point of row-level security. Let us go ahead.
समजलं का? एक report, प्रत्येक audience साठी एक role, share करण्यापूर्वी View as. Pune 40 बघतो. Mumbai 150 बघतो. Row-level security चा हाच मुद्दा. आता पुढे जाऊया.
समझ में आया? एक report, हर audience के लिए एक role, share करने से पहले View as. Pune 40 देखता है. Mumbai 150 देखता है. Row-level security का यही मतलब है. अब आगे बढ़ते हैं.
Frequently asked questions
What is row-level security?
A role filter that limits which rows a person can see in one shared report.
Where do I put the filter?
On the dimension, such as Store[City], so it walks into Sales through the relationship.
How do I test?
Modeling, View as, then tick the role. In the Service, Test as role.
Why does a Member see every city?
Row-level security applies to viewers, not to workspace builders. Give consumers Viewer or an app.
What is dynamic RLS?
One role that compares a column, such as Manager Email, to USERPRINCIPALNAME() for the person who signed in.
Is a hidden page security?
No. Hide rows with a role. A hidden visual is not a security boundary.