How do I write a DAX expression in Power BI for row-level security?

0
votes

I am trying to implement row-level security on one table based on a separate users table. I've seen this talked about in places like this, but haven't been able to get things working for my case.

Users table:

enter image description here

Transactions table:

enter image description here

The table I'd like to secure is called Transactions. One of the fields in each row is CompanyID. The Users table contains three columns: AccountID, UserEmail, and CompanyID. What I'd like is for only users assigned to given CompanyIDs to be able to view rows in the Transactions table with those CompanyIDs.

Since more than one User may view a given row, I established a one-to-many relationship from Transactions to Users on the CompanyID field.

I created a DAX expression that filters on the Users table with the following:

[UserEmail] = USERPRINCIPALNAME()

When I select "View As -> Other User" in Power BI Desktop and enter a random email, though, I can still see the entire report. Any idea what I'm leaving out?

EDIT: I left out an important stipulation: Any user associated with a CompanyID of 1 can view all the records of the Transaction table. I've tried approaches similar to this

  [UserEmail] = USERPRINCIPALNAME() || 
COUNTROWS(FILTER('Users', [UserEmail] = USERPRINCIPALNAME() && [CompanyId] = 1)) = 1

but they don't work. Even users with CompanyId of 1 are prohibited from viewing the table.

1
powerbidaxrow-level-security

1 Answers

1
votes

From the docs:

By default, row-level security filtering uses single-directional filters, whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filtering with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox. Select this option when you've also implemented dynamic row-level security at the server level, where row-level security is based on username or login ID.

enter image description here https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls