Row Level Security for Employee Department

[gopichilla]

Hello, All,

I need help on Row Level Security, Here I have a 10 Departments in my Organization and I don't want to show all data to all departments members here I want to show only related department members data.

Please help on this.

Thanks,

Gopi

[Anonymous]

You'll need a table that maps your AAD logins to their appropriate department. From there, you can do a simple DAX statement in the row level security feature.

Here's the documentation for row-level security:

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

Your DAX might look something like this:

[Department] = LOOKUPVALUE(Departments[Department], Departments[User], USERNAME())

[gopichilla]

Hi, SonnyChilds,

Thank you for quick reply let me check.

Thank you,

Gopi

[gopichilla]

Hi, SonnyChilds,

I have checked your formula it showing all data in desktop and in the service once apply to security for the new role I mentioned user email id and test role it is showing empty data visuals in my dashboard. 

That person has a department in my organization.

How I need to check RLS.

Please help on this and let me know.

Thanks,

Gopi

[Anonymous · Accepted solution]

Honestly, I'd need some sample data from you with some screenshots to investigate further.

Did you add the consumers to the security role in the service? That option is here:

Also, for testing purposes, you can make a couple of measure to help you see what your security actually looks like.

Try this:

Create two new Measures:

User = USERNAME()

Department = LOOKUPVALUE(Departments[Department], Departments[User], USERNAME())

Then add two Card visuals to your report, one each showing the values of these measures. This will show you what the actual username looks like when you log in, and if your Lookupvalue formula actually found a department. If the value doesn't find a department, everything will be blank.