RLS - Limit users belongs only to that department

[selvaskm]

Hi Experts,

I have a users table in my dataset as below. (This table is disconnected)

I need to implement RLS, where users belongs only to the "HR" department must not see any data, whereas the rest of users have to see all the data in the report.

Summary:
User3 and User5  - Data restricted as whole
Remaining Users - Access to All data.

How can we create DAX under role to achieve this.
Pls advice

[v-tangjie-msft · Accepted solution]

Hi @selvaskm ,

First of all, thanks @NaveenGandhi  for the quick reply. Allow me to provide additional insights:
You can use the following DAX:

COUNTROWS(FILTER('user_table',[username]==USERPRINCIPALNAME() && [department] <>"HR"))>0

Then the result is as follows.

Best Regards,

Neeko Tang

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

[selvaskm]

Hi @NaveenGandhi 
Thanks for reply.
In my dataset relationship is not there between user_table and fact_table.
As we need to see "Full data" or "No data" for certain users.

VAR loggeduser = MAXX(FILTER( UsersTable, UsersTable[username] = USERPRINCIPLENAME() ), UsersTable[Department])
RETURN
IF(loggeduser <> "HR", TRUE(), FALSE()
)

it gives expected results when as user is belongs to HR alone (1 record only in usertable),
but if a user have more departments then it will take maximum value.
In that case we may have incorrect access 

[NaveenGandhi]

Hi @selvaskm 

https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

You definetly need a relationship between the secuirty table and you main table. Once you have a relationship follow the above first 8 steps in "Define roles and rules in Power BI Desktop

" section. But in the 6th step use below DAX

NOT([department] = "HR")


Then follow the steps on "Manage security on your model" section in the same article and add the emails of users except HR.

Test the RLS once setup.

 If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!