Create a role that is filter the data depends on the user’s network id ( shows user’s dept info)

[Aa1997]

Greetings All, 

I'm new in Power Bi and I'm facing an issue trying to create a rolr, 

please assist if you know the correct DAX to use, 

I want to create a role that take the current loged in used network ID then go to the (user information table) look up his department then filter all the dashboard with only information of employees of his department

I hope my question is clear,

please note that the (user info table) has relationships with the other tables in the dashboard. 

appreciate your help 🙏 

[bcdobbs]

What you're looking to do is called Dynamic RLS. 
A good starting point is:

https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple

[Aa1997]

Yes i know i have to create dynamic RLS 

but I believe i must use DAX to filter the tables depending on current user department 

please assist if you know the answer 

thank you

[bcdobbs]

I might be missing something I. Your requirement but the details are in the link I sent.

You create your role in power bi desktop and within the role apply a filter to your user table. Something along the lines of:

UserTable[User] = UserPrincipalName()

[Aa1997]

I'm sorry for not providing you with the details 

I have around 6000 employees data in the table

i want to create a dynamic role where each user able to view only the data of the employees in his department. 
let's assume that the table name is user info and it contains 5 columns ( emp name, emp ID, emp department, emp job title, and emp age) 

So I want to create a role whre it looks for current logedin user ID ( in emp ID column) 

then looks what is his department ( emp department column) 

then filter all the data for this department only 

so the user won't be able to view other employees from different departments data. 

I used username() for the network ID but I'm stuck with looking for the department then filter the rows depending on this department 

I hope you help me

appreciate ur efforts 

[bcdobbs]

Ok!

Couple of options. An easy approach is:

In power query duplicate you user table, remove columns so you're left with the username and department. (Remove duplicates if needed).

Then in your model relate your new table to existing table based on department. It will be many to many; set it so your new table filters the user table (really important).

Then apply the user filter to your new table. 

There are other options but iMd try that first.

[bcdobbs · Accepted solution]

I think my above suggest solution is the best way forward from a performance perspective however if you really want to avoid a second table you could use this in the role:

VAR AllowedDepts = 
CALCULATETABLE (
VALUES ( User[Department] ),
User[UserName] = USERPRINCIPALNAME()
)
RETURN
User[Department] IN AllowedDepts

You can see this here:

Example PBIX 

[Aa1997]

Thank you very much appreciate it 🙏