Add Dynamic Role Level Security to PowerBI using current users email

Users logged into PowerBI can have their email address exposed. This allows us to create dynamic row level security using inner joins on a user table.

me@jaykilleen.com wrote this over 6 years ago and it was last updated over 6 years ago.


← Back to the Posts

Add a role called 'User`. Manage that role and add a Table filter DAX expression as follows to the User table.

  User[Email] = username()

You will then need to establish relationships between your User table and other tables that are related to User.

Unfortunately, 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.

Once you have set a user against that role, you can test them using the 'View as Roles' -> 'Other User' and then enter in their email address. This may need to be done on an already published report that has had users added to the role.

Once the user enters the report the User table is filtered by the email address that they used to login to PowerBI. Inner joins are then applied to all tables relating to the User table.

Next stage is to consider managers that need to view themselves plus their users in their heirarchy.