How to configure security?

To configure dynamic row-level security (RLS) based on the logged-in user and their category in the Members table, follow these steps:

Steps to Implement Dynamic Row-Level Security:

1. Ensure the Data Model is Set Up Correctly:

   • Table A: Your main data table with the Category column.
   • Table B (Members): The table with user information (UserEmail, IsUserAdmin, CategoryLeader).

   Ensure that Table A is related to Table B through the Category column (i.e., Table A[Category] should be related to Table B[CategoryLeader]).

2. Create Roles and Filters in Power BI:

   • Open Power BI Desktop.
   • Go to the "Modeling" tab and select "Manage Roles."
   • Create a role for regular users and admins.

3. Filter by Logged-in User: For the role that filters data based on the user's category, you'll want to apply a DAX filter that dynamically checks the logged-in user’s email and their category in the Members table.

   • Non-Admin Role: Use the DAX expression in the role to filter based on the logged-in user's email and category:

     [Category] = LOOKUPVALUE('Members'[CategoryLeader], 'Members'[UserEmail], USERPRINCIPALNAME())

     This will filter Table A to show only the category that corresponds to the logged-in user's email.

   • Admin Role: For the admin role, you can use a DAX expression that checks if the user is an admin:

     'Members'[IsUserAdmin] = TRUE() || [Category] = LOOKUPVALUE('Members'[CategoryLeader], 'Members'[UserEmail], USERPRINCIPALNAME())

     This will allow admins to see all the data, while non-admins will only see data for their category.

4. Testing the Roles:

   • After creating these roles, use the "View As" feature to simulate different users' perspectives to ensure the filters work correctly.

5. Publish and Assign Security:

   • Once the report is ready, publish it to the Power BI Service.
   • In the Power BI Service, go to the dataset settings, and under "Security," assign users to the roles you created.

This setup will dynamically filter data based on the logged-in user's email, ensuring that admins can view all data, while non-admins are restricted to the categories they lead.

so its working in power bi desktop but not on power bi service? try clearing cache of the report and the browser and publish again.