Want to learn how to test dynamic row-level security in a Power BI Desktop file? In a previous post, I showed how to set up dynamic row level security which you can check out here.
At the end of that video, we ended up with a nice rule for imparting that security in the file. One foundation of this rule is the use of a DAX measure, either the UserPrincipalName or Username. Check out my video demo included below where I’ll walk through the testing process.
- When we are in the Power BI Desktop, these two measures will return something different than when we’re in the Power BI Service. In the Desktop it will return the domain of the user who is logged into the physical laptop. In the Service, it will return the email address of the user that is logged in. That email drives all the security that goes through this file.
- If we want to test this in Power BI Desktop, I go under the Modeling tab. But when I click on View as and then click the role I want to test and kick it off without any other inputs, it will not work. This is because UserPrincipalName and Username are not returning an email address, instead they are returning the domain and user of the physical machine.
- Even though I’m logged into Power BI Desktop, this is not the correct input, so I’ll need to customize the input by putting the email address that’s powering the security of this file.
- When I change my input to an email address, I see that it will override the two measures and force in that email address instead of the domain of the user. This is the experience you would get if you were logged into the Power BI Service. What I see now is a good indication that the security is working.
- If I want to test another user, I can simply change the inputs in that View as field and we’ll see the file adjust accordingly.
- Another thing to check is the Table view. You’ll see that the tables themselves are filtered down. If you click through your filters and see a table that you expect to be filtered but it is not, you should go back into your Manage Roles and add more filters if necessary. This will ensure your users are seeing information you want them to see or not seeing more info than they are allowed.
- Once you have tested this in the Desktop file, you can feel assured that it will work as you intended when you move this to the Power BI Service.
I highly recommend testing first in Power BI Desktop, before engaging in any testing in the PBI Service, because in this format the file is free of other influences (i.e. workspace permissions, security memberships, dataset permissions, etc.) that may impact what that user can see. In short, if it works in Power BI Desktop, you can be confident that you’ve got it in the right position to move it into the Service and you’ll know it’s working at its core most granular level in this file.
Look for my next post where I’ll do the same exercise in the Power BI Service. If you have any questions on row-level security, Power BI, or Azure in general, we’re here to help. Contact us or click below to speak to any of our Azure experts.