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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.