Take the currently logged in user in PowerBI and filter data based on an attribute of the User table
me@jaykilleen.com wrote this about 6 years ago and it was last updated about 6 years ago.
In my last blog post we looked at adding a User role in PowerBI and filtering all data where it relates to the currently logged in user.
In the next scenario, we want to filter the User table based on the Team that the currently logged in user belongs to.
This is great for a Manager role type where you want the manager to see all the data that relates to themselves or members of their team.
Add a role called 'Manager`. Manage that role and add a Table filter DAX expression as follows to the Team table.
[ID] = IF(CALCULATE(SUM(User[TeamID]),FILTER(User,User[Email]=USERPRINCIPALNAME()))=0,999999,CALCULATE(SUM(User[TeamID]),FILTER(User,User[Email]=USERPRINCIPALNAME())))
What this does is sums the Filter the Team.ID based on the User.TeamID of the currently logged in user. This should return just one row of data and so the SUM should work. If you accidentally had two users with the same email that this would not work. I also add a catch on anyone that doesn't have a TeamID. SO that if it sums to 0 then I pass in 999999 which is just some arbitrary value where I know that the Team table would not return a result for.
You will then need to establish relationships between your User and Team table as well as the other tables that are related to User that you want scoped down based on this RLS data ownership.
Once again, you will need to manually add all users to this User role that you want this type of RLS to be applied to. Vote on this PowerBI feature to set a default role for PowerBI users.