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
GraceTCL
Helper II
Helper II

Row level security

Hi

I am attempting another formula to implement row level security by following steps at link: https://blog.pragmaticworks.com/dynamic-row-level-security-in-power-bi

 

Unfortunately, it doesnt work as I am still seeing the full set of data even when I am viewing as a user with my own login.

 

This is the formula that I have applied and my sample file enclosed at link. Hope someone can advise.

Capture3.PNG

Thank you.

5 REPLIES 5
v-cazheng-msft
Community Support
Community Support

Hi @GraceTCL 

Not sure about the table structure and relationships in your model. But I create a sample according to your description. You can make some regulation according to your model.

 

Here is the sample data.

Employee table:

v-cazheng-msft_0-1620711588390.png

 

Access table:

v-cazheng-msft_1-1620711588391.png

 

FoodMetrics table:

v-cazheng-msft_2-1620711588420.png

 

The relationships between these tables:

v-cazheng-msft_3-1620711588423.png

 

In this sample, RLS is applied on Access table. If you want Employee table filter data according to the RLS, you need set the cross filter direction to Both and check ‘Apply security filter in both directions’.

v-cazheng-msft_4-1620711588424.png

 

Set RLS on Access table:

v-cazheng-msft_5-1620711588425.png

 

The result looks like this:

v-cazheng-msft_6-1620711588426.png

 

v-cazheng-msft_7-1620711588428.png

 

 

When you publish the report to Service, you need add members to the RLS. There is one thing you need remember that RLS will not restrict the user who has Edit permission to the report.

 

If you still have questions, please let me known.

 

Best Regards

Caiyun Zheng

 

If this post helps, please consider make it as the solution by Accept it as Solution. Really appreciate!

HI @v-cazheng-msft 

 

I had applied both directional relationship as you advised but it doesnt work. In the model, I dont have "subvertical ID" in the fact table actually and I am wondering if I need to include it through merged queries in order for row level security to work. However, I am concerned to add as this means a column with millions of row and hence increased file size. Is there anyway to avoid this?

Below is the sample model where the row level security is still not working. Hope you can advise what is required to change to make it work without having to add "subvertical ID" to the fact table.

 

https://www.dropbox.com/s/s6ushpfeuo9sz3t/Food%20cleansed.pbix?dl=0

aj1973
Community Champion
Community Champion

Hi @GraceTCL 

In the Blog video, the guy used "IN" Capitol letters. Your formula is using "in".

Also before the LOOKUPVALUE in your formula you are missing to call the column from Access table and add "=" before LOOKUPVALUE.

aj1973_0-1620571259391.png

 

Last code you don't need to call for the name of the table

aj1973_1-1620571343213.png

 

Before you insert the formula into RLS, try to add it as a measure and see if it works, just like in the video.

 

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

hi @aj1973 I did but it still doesnt work even on real data. I am not sure if I need to filter directly on FoodMetrics table but am not sure how to do that.

aj1973
Community Champion
Community Champion

@GraceTCL 

Not sure either what do you want to achieve! I tried to correct the formula that you have been using, eventhough I know that not every formula from one model can work on an other model, I mean the formula worked well for the Guy in the video, doesn't mean can work on your model.

 

I recommend that you try creating measures one by one and see the result, then you can nest those measures into a one formula untill it works. Or use the filters in "Manage roles" untill you get what you need.

 

I know that data in your model is sensitive or I would have asked for a Sample Pbix, but i am trying my best to help you.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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.