RLS Question

[abhiram342]

Hi Team,

I have below model and don't want to show Customer Table If User is not part of Security Table but user should able to slice by other pivots ( Geography). Because Of Default security filtering , It's not working. Can you please tell if there is any workaround.

Scenario : If User is not part of Security Table then don't want to show customer data but user should able to see data by Geography.

RLS Definition On Customer Table:

IF(ISEMPTY(FILTER(VALUES(Secuirty),AND(Secuirty[User Alias] = LEFT(USERPRINCIPALNAME(), FIND("@", USERPRINCIPALNAME()) - 1),Secuirty[Is Authorized User]))),FALSE(),TRUE())

PBI models

Thanks,

Abhiram

[123abc]

In Power BI, you can achieve the desired behavior by modifying your Row-Level Security (RLS) expression. It seems like you want to restrict access to the Customer Table based on whether a user is part of the Security Table, but still allow them to slice the data by Geography. To achieve this, you can use a combination of DAX measures and roles. Here's a step-by-step guide:

1. Create a New Measure for Geography Slicing: You want users to be able to slice data by Geography regardless of their authorization status. Create a measure that calculates your desired metric for Geography. For example, if you have a Sales measure, create a Geography Sales measure:

Geography Sales = SUM(Customer[Sales])

Modify RLS for Customer Table: Modify your RLS expression on the Customer Table to include a condition that checks if the user is authorized to see data by Geography. You can use a combination of the USERNAME() and USERPRINCIPALNAME() functions to check for authorization.

Here's a modified RLS expression:

IF(
ISEMPTY(FILTER(VALUES(Security), AND(Security[User Alias] = LEFT(USERPRINCIPALNAME(), FIND("@", USERPRINCIPALNAME()) - 1), Security[Is Authorized User]))),
FALSE(),
TRUE()
)
||
HASONEVALUE(Geography[GeographyColumn])

1. In this expression, we added HASONEVALUE(Geography[GeographyColumn]) to allow users to see data if they are slicing by Geography. If they are authorized for a specific Geography, they will see the data; otherwise, they will see nothing.

2. Create Roles: Create two roles in Power BI: one for authorized users and one for others.

   • Role 1 (Authorized Users): Assign this role the modified RLS expression for the Customer Table.
   • Role 2 (Other Users): Assign this role no RLS restrictions for the Customer Table. They will only be able to see data by Geography.

3. Assign Users to Roles: Assign users to the appropriate roles based on their authorization status. Users in the "Authorized Users" role will see customer data based on the RLS expression, and users in the "Other Users" role will see data only by Geography.

This approach should allow you to achieve the desired behavior: restricting access to the Customer Table unless the user is authorized while still allowing them to slice the data by Geography.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.