This works perfectly fine when AD group is assigned to all the roles in Power BI service.
However, there are few users in the AD group that needs to see everything irrespective of RLS filter but are not part of the Users Hierarchy table. I created a role "All Roles" where 1=1 and added AD group to it.
Although it works for "All Roles", the RLS roles filters aren't working anymore.
It seems like the problem is caused by the creation of the "All Roles" role that doesn't have any restrictions. When a user is a member of this role, they can see everything in the report, regardless of the RLS filters defined for other roles. To resolve this issue, you can create a separate role for those users who need to see everything, and give them access to all the data in the report without using RLS.
Alternatively, you can modify the security predicates of the existing roles to include the conditions that allow the desired users to see everything. For example, you can add an OR clause in each security predicate that checks if the current user is one of the users who need to see everything.
Here is an example of how you can modify the security predicate for the "Executive" role:
[Executive Email]= userprincipalname() OR [Email]= "<specific email of the user who needs to see everything>"
Note: Replace <specific email of the user who needs to see everything> with the actual email of the user who needs to see everything. You can do the same for other roles as well.
By modifying the security predicates in this way, you can ensure that the desired users can see everything while still using RLS to restrict access to the other users.
Thank you for your response. As I mentioned in my request, I have already created "AllRole" with no RLS i.e 1=1but it seems to apply the 1=1 for all users and show everything to executives, Head of , etc without checking username.
Basically, if the login user is not in the User Hierarchy table then he should see everything.