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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.