cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors