Forum Discussion
Row Level Security using User Principal Name (UPN)
Never mind. I figured out what I was doing wrong. I think part of my brain is already floating on the lake, totally NOT thinking about Power BI. :-)
I just forget to both click "View As" and also select the role. Again, total brain fail I am blaming on the upcoming holiday.
Thanks! Scott
Hi
Can you elaborate what you mean by row level security is not working. How are you testing this? You would create the measure under Modelling> Manage Roles. Be sure to pick the correct table this measure should filter against
You may find this useful
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
After publishing the report with the role Test with the filter [E-mail Address] = userprincipalname() did you add the users to the Test role in the Power BI service?
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
One way that makes it easier is to add an active directory security group to the role. You can also share the report with the security group. That way, when a new employee comes on, if they get added to that security group, the report is already shared with them and RLS is applied.
One other note, for the long list of filters, instead of
[Group Company Code] = "DE14" || [Group Company Code] = "DE12" || [Group Company Code] = "DE11" || [Group Company Code] = "DE10" || [Group Company Code] = "DE09" || [Group Company Code] = "DE08" || [Group Company Code] = "DE07" || [Group Company Code] = "DE06" || [Group Company Code] = "DE05" || [Group Company Code] = "DE04" || [Group Company Code] = "DE03" || [Group Company Code] = "CH05" || [Group Company Code] = "CH03" || [Group Company Code] = "CH02" || [Group Company Code] = "AT03" || [Group Company Code] = "AT0220" || [Group Company Code] = "AT0210" || [Group Company Code] = "AT0200"
you can use the new IN function
[Group Company Code] IN {"AT0200", "AT0210", "AT0220", "AT03", "CH02", "CH03", "CH05", "DE03", "DE04", "DE05", "DE06", "DE07", "DE08", "DE09", "DE10", "DE11", "DE12", "DE14"}
Hi both,
Apologies for the delayed response. Thanks for the tip re the IN function.
Because Table A and Table B have a relationship, what I mean by row level security not working is that when I publish the report to the Power Bi Service, or select View as Roles in Desktop mode, I expect Table A to display only data relevant to the e-mail address of the person who opened the report (via the userprincipalname expression). As noted in more detail in my first post, if I select an e-mail address from Table B in Desktop, Table A correctly displays data relevant to the selected e-mail address in Table B (because of the relationship between the tables).
One possible clue is that I have created this role against Table B (Security Mapping) rather than Table A (FPI & Accounts). I have done this because it is that table which has the e-mail address.
I hope this is clearer.
Best regards,
