cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adayweld
Frequent Visitor

Not able to get RLS to work on a second table

Hoping someone can help me with row level security here. I have a budget table and a transactions table which are linked together in the data model. My budget table rls is working and is applied to org codes ("ORGN_CODE_KEY" & "ORGN_CODE_1")to allow users to see gross financials. The transaction table is an itemized listing by transaction record which still uses org codes ("ORGN_CODE").

 

I am following the steps Manage Roles > Right click by my transaction table > choose the "ORGN_CODE" option > select the applicable code "26500" and click save. All I get in return when I "view as role" is a blank screen, no error code or anything. Any ideas?

 

 

 

 

2 ACCEPTED SOLUTIONS

Ah, I see, so you should in best practise use the same security filter as in certain cases one will counter act the other. If you disable that security filter does this one work? What are you filtering on the other one?

View solution in original post

adayweld
Frequent Visitor

Thanks for your assistance on this one. I deleted the relationship between the two tables and the RLS works well independently. You touched on what I believe to be the issue though, that is the RLS should use the same security filter or one will counter the other. In addition, if the tables do not need to be related then independent RLS is possible. 

 

Thanks again!

View solution in original post

12 REPLIES 12
rajendran
Super User
Super User

Hi @adayweld

 

When you define RLS, you filter the table for 26500. Please make sure your visual has this value, if not Power BI is going to show a blank visual,So please confirm that part.

 

Thanks

Raj

Thank you for the response. I have added the ORGN_CODE column to my table visual and no luck. Still loads a blank page only. Here is my DAX expression: [ORGN_CODE] = "10100"

 

Here is what loads when viewing as role.

 

Screenshot.PNG

Are you sure [ORGN_CODE]  has the value of 10100 ?

 

Another point to note is - If you apply the RLS in the master table, the crossfiltering will pass to the transaction table but the vice versa wont happen.

 

Thanks

Raj

Yes, [ORGN_CODE] has the value of 10100. I have also noted your filter point as well. Thanks for the reply overall however, I still am unable to get this working. As a troubleshooting measure I've tried filtering by other columns on the transaction table and no luck with anything I seem to try there. 

 

Any other ideas?

 

Thanks, 

 

aday

Hi @adayweld

 

There are a few things that may need to be set here, can you share the pbix file by any chance? Appreciate it may be sensitive data.

 

Thanks

 

shebr

adayweld
Frequent Visitor

I'm sorry I cannot share the file (sensitivity) however, what settings are you speaking of?

Hi @adayweld

 

A couple of things you could check to begin with, the data type of the value you are filtering. Ensure this is a text value accross the board.

 

Also, add the column to the page filter of your report and test that it works by just selecting and applying that filter.

 

Can you share the code you have used in your role filter please?

 

Thanks

 

shebr

adayweld
Frequent Visitor

Verified text column values within both tables. Also added the "ORGN_CODE" filter at the page level, which does filter down the results to 10100 only. 

 

Here is the DAX code I've tried:  [ORGN_CODE] = "10100"

 

Thanks for the assist!

Hi @adayweld

 

Ok great, so then im assuming you select the 'view as' options and select that role and you dont see the filtered value?

 

Can you check your relationship to this table next, double click on the relationship and there is an option where you can select 'apply security in both directions'. Click this and test the 'View as' role again. 

 

Let me know how you get on.

 

Thanks

 

shebr

adayweld
Frequent Visitor

Correct. I checked the relationship on the transactions and selected the 'apply security in both directions option and received the following error:

 

 'Table 'other_table_name' is configured for row-level security, introducing constraints on how security filters are specified. The setting for Security Filter Behavior on relationship '659f392c-a2c8-45dd-b69a-8319bc2d9a81' cannot be Both.

 

Just to be clear, there is active RLS on the other table which is already working correctly. 

Ah, I see, so you should in best practise use the same security filter as in certain cases one will counter act the other. If you disable that security filter does this one work? What are you filtering on the other one?

adayweld
Frequent Visitor

Thanks for your assistance on this one. I deleted the relationship between the two tables and the RLS works well independently. You touched on what I believe to be the issue though, that is the RLS should use the same security filter or one will counter the other. In addition, if the tables do not need to be related then independent RLS is possible. 

 

Thanks again!

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors