Adding a restriction to RLS

[z-rising]

I have a PBI desktop report I publish to service that uses RLS.

The current configuration has a table "RLS" that refreshes an excel file with the columns "Username:, Division", and "Email".  The table has a many to one cross-directional filter in both directions with security filters in both directions, connected to a table "Division" that has a single column "Division II" listing the 10 divisions of the company.  This table connects to both main data tables "Data 1" and Data 2" via a many to one single direction cross filter.

The RLS currently works correctly, using the DAX code  [Email] == USERPRINCIPALNAME() in a security role applied to the RLS table, so that when they log on to PBI service, it only allows them to view the data from divisions that are adjacent to their email in the RLS table (some users have access to more than one division, so they have a row int he RLS table for each division they have access to).

I have a request from the manager of the group to add an additional filter to restrict access to one account "Cost of Sales" for users who are in 4 of the divisions - "DivC" "DivD" "DivF" and "DivG".  In the Data 1 and Data 2 tables, the columns the account names are named "Account Name" and "Account Description" respectively.

I am trying to write a DAX code to add to the filter in Security Roles that will restrict users from those 4 divisions from seeing the account "Cost of Sales" but haven't been able to figure it out.

Please let me know if I can supply any additional information.

[audreygerred · Accepted solution]

Hi! Row Level Security (RLS) only restricts rows. If you would like to restrict columns and such you will need Object Level Security (OLS) which can be achieved using Tabular Editor 2. Here is a link to explain in much greater detail: Object-level security (OLS) with Power BI - Microsoft Fabric | Microsoft Learn

[z-rising]

Thank you, I'm looking into OLS now.  First time in Tabular Editor, wish me luck.

[Greg_Deckler]

Here's an alternative to OLS that doesn't break the visual if the user doesn't have access to the column:

Column Level Security - Microsoft Fabric Community

[z-rising]

Thank you fo rthe additional info - I don't belive I'm restricing any columns, just rows of data ($ soecifically) that don't match the division they are assigned to in the RLS file

[audreygerred]

Very interesting! Thank you for sharing!

[Greg_Deckler]

@z-rising Be aware that RLS uses a least restrictive model when it comes to users who fall into multiple roles. Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn

[z-rising]

That's interesting.  It's been working correctly for me so far in the user access, I haven't had any feedback that anyone can see (or not see) other divisions.