If you’d like to download a copy of the PBIX file from what follows, I’d be happy to provide it to you. See instructions at the end of this post.
Dynamic Row Level Security
Implementing dynamic row level security (RLS) for each table in a Power BI data model based on someone’s username can be tricky to understand, let alone implement.
I’ve created a generalized example based on the Power BI Solution Template for Sales Management that has both a User and User Ascendants table.
The idea is to restrict each User to see only records in the Opportunity and Opportunity Product tables where they are the designated Owner for the associated Account.
Additionally, anyone listed as a related “User Ascendant” can see their records, as well. An “Ascendant” is anyone with a higher and related level in the organization. So the Salesperson, their Manager and their Director can all see the Opportunities owned by that Salesperson. The Manager can see all Opportunities owned by all their Salespeople, and the Director can see all Opportunities owned by all Salespersons reporting to their Managers.
The key is for the Office 365 login id of the Power BI User to be stored in the User Ascendants table, where it can be accessed and compared to the output of the USERNAME() function.
How to Test
To test this example from Power BI Desktop, open the PBIX file and choose Modeling >> View as Roles. Choose both role “test4” *and* “Other user”. For Other User, type in any of the email addresses shown in the greyed out multicard “Email/Login reference by User[FullName]”. With each email address you enter, you will see what dynamic RLS allows each User to see in the Opportunity and Opportunity Product tables.
You can see the DAX expressions built into measures used for the implementation, as well as test what subset of all data each username would see when they are logged in. Measure “test4” is the final product, where measures test, test2 and test3 are intermediate measures built along the way.
If you’d like a copy of the PBIX file that shows the data model and DAX expressions for your own reference, drop me a line at firstname.lastname@example.org and I’ll send it to you.