Row Level Security for only a specific number of users

[azl624o]

Hello - I am trying to create a row-level security that generates specific data for 73 users, but allows all other users to view the full range of data. I am using the following formula:

If (
LOOKUPVALUE(
'Sheet1' [Title],
'Sheet1' [Username], USERPRINCIPALNAME()
)=1,
[Username]=USERPRINCIPALNAME(),
TRUE()
)

This works when I am in PBI Desktop as the data changes when I view as someone in [Title] and someone not in [Title]. When I published to the server, i was getting an error on every box saying, "The report can't be viewed because the underlying dataset uses row-level security(RLS)". Do you know why this would work in Desktop and not in the Server? Do you have any tips in creating a RLS that only limits the view of data for a certain number of users and allows all else to view the entirety of the data set?

Thanks in advance!

[v-ssriganesh]

Hi @azl624o,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @azl624o,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh · Accepted solution]

Hello @azl624o,
Thank you for reaching out to the Microsoft Fabric Forum Community.

To implement RLS where 73 salespeople see only their own data and all other users see the full dataset, you can use dynamic RLS with a DAX formula or create two roles (one for salespeople and one for full access). The error you’re seeing in the Power BI Service indicates that users may not be assigned to an RLS role, which is required for reports with RLS enabled.

Here’s a recommended approach:

• On the 'Sheet1' table, create a role with this DAX filter:

VAR UserExists =

    CALCULATE(

        COUNTROWS('Sheet1'),

        'Sheet1'[Username] = USERPRINCIPALNAME()

    ) > 0

RETURN

IF(

    UserExists,

    [Username] = USERPRINCIPALNAME(),

    TRUE()

)

This restricts data for users in 'Sheet1'[Username] and allows full access for others and publish the report to a workspace. Go to the dataset’s Security settings, and assign all users (or a security group) to the role. Ensure all users have report access (via workspace Viewer role, app, or sharing).

Verify that 'Sheet1'[Username] matches USERPRINCIPALNAME() exactly (case-sensitive). Use LOWER([Username]) = LOWER(USERPRINCIPALNAME()) if needed and Use an Azure AD security group for the 73 salespeople to simplify management. Test with the Test as role feature in the Power BI Service.

For detailed steps, refer to the official Microsoft documentation:
Row-level security (RLS) with Power BI this guide covers configuring RLS roles, testing, and managing security in the Power BI Service.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @azl624o,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[powerbiexpert22]

Hi @azl624o ,

please see below threads

https://community.fabric.microsoft.com/t5/Desktop/RLS-Allow-certain-users-to-see-all/td-p/2111304

https://community.fabric.microsoft.com/t5/Desktop/PBI-Row-Level-Security-for-different-user-types/td...

[powerbiexpert22]

Hi @azl624o ,

you did not mention criteria ( in your data) for RLS . example one of the criteria is users belong to US ( country column in data) should see US data , similarly another criteria is users belong to China should see China data so what is the criteria for applying RLS in your data?

once you identify the criteria then user below logic

if login user match entry in excel then show only data specific to user ( means apply RLS criteria)

else show all data to login user

[azl624o]

Thank you for the response. I have a list of sales opportunity data and a list of sales people (Sheet1) that have a many to one relationship. 

I want the salespeople to only see their own sales opportunity data when they view the dashboard. I also want anyone who is not a sales person in Sheet1 to be able to view the entirety of the dashboard, not a sliced down version based on a single sales person. 

Basically I am looking for: If username (email) is in sheet1 then only show that user's data else if username not in sheet1 then show all data.

Does that help?