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
BW40
Helper I
Helper I

How to get one table to automatically filter another

I am trying to build a report where when users log in, their email filters their view automatically so their only see their or their staff's data.

 

I used PRINCIPALUSERNAME( ) as a measure to automatically show the email of whomever is viewing the report. I then used that as a filter for a second visual with all of the company's emails so it only shows the user's email. The second table works so that table pulls up only the viewer's email.

 

User = USERPRINCIPALNAME()
User Filter = IF(SELECTEDVALUE(Staff_Member[Staff_eMail_Address])=USERPRINCIPALNAME(),1,0)
 
Next, I have a third table that has a list of all of area managers and the local managers. When I click one of the names on the managers' table it filters a fourth table to only show their clients served.
 
**How can I get the second table (emails) to autmatically fitler my third (mangers') table so it filters the fourth table and they only see their stafff's clients?**
 
An example management structure looks like this where I only want regional manager A to log in and see managers a - g and their clients 1 - 7 while manager a would log in and only see client #1.
 
 BW40_0-1700172521712.png

 

Is there an easier way to do this? The goal is to let whoever logs in only see their clients or the clients of those they supervise.

3 REPLIES 3
BW40
Helper I
Helper I

@amitchandak the problem is the email addresses come from a secondary table which has not been allowing me to filter the main table.  I am trying to find an alternative method because the traditional RLS method was not affecting the main table.

BW40
Helper I
Helper I

@amitchandak I started with using dynamic RLS but when that did not work I looked into alternative routes to get the desired results. RLS roles are not filtering the data based on user (my user name sees everything and when I view as another user the filter does not affect the table).
The closest I can get is using IF(SELECTEDVALUE(Staff_Member[Staff_eMail_Address])=USERPRINCIPALNAME(),1,0) as a filter for the intermeding table but I cannot get the second table to filter the overall client count table. 

amitchandak
Super User
Super User

@BW40 , We usually use RLS, we join table with table haviing access and email and then use code of first measure

example

[email]=USERPRINCIPALNAME()
Power BI- Row Level Security(RLS): Handle ALL, UserPrincipalName: https://youtu.be/KVLEnIUo4pc

 

For Hierarchy

 

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

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.