Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IoanaSluby
Frequent Visitor

Configure Row Level Security with OR condition

Hi!

 

I have a main datasource which contains data about worklogs, grouped by month/year/project and employee, with these fields:

- Employee

- Project

- Month 

- Year

- Time in hours 

- Employee division

- Project Division

And one RLS tables with these fields:

- division (which can be either employee division or project division)

- User (email address)

 

The role that i created is based on the rule that User = USERPRINCIPALNAME()

 

How could I simulate an or condition so that for a specific user data could be displayed either when employee division = division from RLS or project division = division from RLS?

 

The relation between tables is many to many, a user can be assigned to multiple divisions.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,

 

You can apply RLS to the main table (WorkLogs) instead of the RLS table and check like this:

= CONTAINS('RLSTable', 'RLSTable'[User], USERPRINCIPALNAME(), 'RLSTable'[division], 'WorkLogs'[Employee division])

|| CONTAINS('RLSTable', 'RLSTable'[User], USERPRINCIPALNAME(), 'RLSTable'[division], 'WorkLogs'[Project division])

 

You don't need any relations between the two tables. Caching of RLS will stop working if you have more than about 130 000 rows, but if the table is not too big it will probably work even if you exceed that number.

 

If performance is not good enough with the above solution, my suggestion is to add some kind of bridge table between the WorkLogs table and RLStable that connects worklog rows with rows in the RLS table.

 

Best Regards // Ulf

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Hi,

 

You can apply RLS to the main table (WorkLogs) instead of the RLS table and check like this:

= CONTAINS('RLSTable', 'RLSTable'[User], USERPRINCIPALNAME(), 'RLSTable'[division], 'WorkLogs'[Employee division])

|| CONTAINS('RLSTable', 'RLSTable'[User], USERPRINCIPALNAME(), 'RLSTable'[division], 'WorkLogs'[Project division])

 

You don't need any relations between the two tables. Caching of RLS will stop working if you have more than about 130 000 rows, but if the table is not too big it will probably work even if you exceed that number.

 

If performance is not good enough with the above solution, my suggestion is to add some kind of bridge table between the WorkLogs table and RLStable that connects worklog rows with rows in the RLS table.

 

Best Regards // Ulf

I think because of the relation (many to many) it gives me an error: A single value value for column Employee division cannot be determined. This can happen when a measure refers to column that contains many values without specifying an aggregation.

 

 

sturlaws
Resident Rockstar
Resident Rockstar

Hi @IoanaSluby 

 

how to do this will depend on your data and the desired output. Could you provide some relevant sample data and a mockup of your desired output?

 

Cheers,
Sturla

 

 

Datasource.jpg

Currently, there is a relation between the RLS table and Project Division (RLS filtering Main datasource) and a role which says that User = USERPRINCIPALNAME().

What i would like to achieve is displaying for User = user1@gmail.com, since he has rights on Commerce division, both the rows where Employee Division = Commerce or where Project Division = Commerce.

Anonymous
Not applicable

@IoanaSlubyDid you get the error message when you tried the DAX I posted earlier? It should be on the main data source table, not on the RLS table. And you should remove the relationship.

 

RLS expression on Main Data source table:

= CONTAINS('RLS Table', 'RLS Table'[User], USERPRINCIPALNAME(), 'RLS Table'[Division], 'Main Data source'[Employee division])

|| CONTAINS('RLS Table', 'RLS Table'[User], USERPRINCIPALNAME(), 'RLS Table'[Division], 'Main Data source'[Project division])

 

Best Regards // Ulf

@Anonymous , thank you very much, I tried again and the error does not appear, and tested as role and everything seems to work fine! My main datasource has currently 120 000 rows, but it will keep growing. Doesn't seem an issue at this time but maybe i will reconsider this.

I have exactly the same issue, and I'm not an expert on Power Bi.  Would you please give me specific instructions on where would I put the above expression?

 

Anonymous
Not applicable

Happy to hear it worked! Please remember to mark my answer as accepted solution 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors