Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
VeenaAnalyst
Frequent Visitor

RLS to show all records of department to which an employee belongs to

I want to show all records of department to which an employee belongs to as well as direct and indirect reportees.

I was able yo achieve the direct ad indirect reportees part but unable to show all records of department.Below is the table structure.

VeenaAnalyst_0-1712205491046.png

1.If Leia Organa logs in the system she will see data for direct reportees Kylo Ren and Darth Maul and indirect reportees Ana. This is achieved.

VeenaAnalyst_1-1712205597519.png

2.Now 2nd requirement is Leia Oragana should see data for all records having similar department i.e Dept 3

So It will include Obi-wan .

3.3rd requirement there is another table department where the employees are not enterd in Manager table , but since they belong to some department ,employees from Manager table should see related department data from department table. Robert will be included.

Final output should be 

VeenaAnalyst_2-1712205653368.png

Immediate help would be appreciated!

4 REPLIES 4
VeenaAnalyst
Frequent Visitor

I joined 2 filters using or condition and it worked for first 2 conditions.

[Department] = Calculate(MAX([Department]),FILTER(Manager,[Employee email] = USERPRINCIPALNAME())) || PATHCONTAINS([Hierarchy path], [current userid])

VeenaAnalyst_0-1712250560084.png

VeenaAnalyst_1-1712250646717.png

Now we are left with one requirment. record from department table. Robert form department table should also reflect. For that could anyone suggest the join/model.

 

VeenaAnalyst
Frequent Visitor

Additonl info for what i achieved with screenshot : I am getting expected output for first requirment direct and indirect reportees using Sales table filter
PATHCONTAINS([Heirarchy path id], [current userid]) = True

VeenaAnalyst_4-1712242907280.png

Getting expected output for second requirment. using filter [Department] = Calculate(MAX([Department]),FILTER(Manager,[Employee email] = USERPRINCIPALNAME()))

VeenaAnalyst_5-1712243047248.png

But If I used both its elimating records and showing data for kylo ren and leia.

 

ALso 3rd requirment is it should show records from department table also. Let me know the joins toachieve this

Greg_Deckler
Super User
Super User

@VeenaAnalyst Can you post your sample data as text?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thank Greg!

Below is sample data.

Manager table:

VeenaAnalyst_0-1712242406875.png

Sales Table:

VeenaAnalyst_1-1712242445857.png

Department Table:

VeenaAnalyst_2-1712242561029.png


I have created two measure user id and Current user id
1.user id = USERPRINCIPALNAME()

2.current userid = LOOKUPVALUE(Sales[Employee ID],Manager[Employee email],Manager[user id])

Also I created calculated column
Heirarchy path id = PATH(Sales[Employee ID], Sales[Manager ID])

 

Create one Role with 2 filters
Sales table filter
PATHCONTAINS([Heirarchy path id], [current userid]) = True

Manager table filter
[Department] = Calculate(MAX([Department]),FILTER(Manager,[Employee email] = USERPRINCIPALNAME()))

If I create only sales table filter then I can see data for reportees and direct reportees for Leia Organa
If I create only Manager table filter then I can see all records for same department for Leia Organa
But If I applied both its not giving correct data.

Also there is one department table with dept name and emp id. My report should show record from the department table as well.

 

So If Leia Organa logs in below should be final output.

VeenaAnalyst_3-1712242779114.png

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors