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.
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.
The relationships between these tables:
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’.
Set RLS on Access table:
The result looks like this:
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.
If this post helps, please consider make it as the solution by Accept it as Solution. Really appreciate!
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.
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.
Last code you don't need to call for the name of the table
Before you insert the formula into RLS, try to add it as a measure and see if it works, just like in the video.
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.
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.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!