Forum Discussion
Row Level Security - MdxScript(Model) (45,60) Calculation error in measure
Hi everyone,
I have developed a dashboard which has 9 pages and published it to BI Service.
The requirement was to set up page level access restriction but since this is not possible with Power BI, we tried RLS. ( to restrict the visuals)
I created a view in Snowflake ACCESS_CONTROL_VIEW which has below columns. The storage mode is mixed as I am using time dimension in my semantic model as well.
The ACCESS_CONTROL_VIEW has following columns -
user_id STRING NOT NULL,
page_name STRING NOT NULL,
restricted INTEGER NOT NULL
Once, I set up RLS for the view, I created Access measure to restrict access to visuals.
ACCESS = IF(LOOKUPVALUE(ACCESS_CONTROL_VIEW[RESTRICTED], ACCESS_CONTROL_VIEW[USER_ID],USERPRINCIPALNAME())=1,1,0)
I gave access to all users at first & RLS worked as expected -
Later I changed the restiction for user B and it did not work properly.
After updating the table & refreshing the view -
User B should not be able to see P3 but is able to view P3 and they should be able to see P1 & P2, but they are unable to view and this is the error screenshot -
This is working as expected in Power BI Service when I test it out using other role, but it is not working properly in Power BI Service.
I tried refreshing the semantic model, re-publishing it, tried to re-do the RLS, there are no duplicate values for a given user. Looking for any other suggestions. Thankyou.
9 Replies
RLS is restricting access to rows, not to visuals. All users will see all visuals, but for some users the UX will be bad as the visuals will have no data.
Hi Ibendlin,
Thankyou for your response. Yes, I an aware. My idea was to restrict data whenever a user does not have access( as specified in the access_control_view), they will be able to see the visual but not the data. This solution was fine for our requirement as we don't want to expose some of the financial metrics. For example, this is how it looks when a user doesn't have access.
The measure Access has been applied to filter for each visual.
I know this is not a direct way of doing things, but found this workaround and it is working for others who have implemented this but stopped working for me a little later.
If your users represent sufficiently different audiences and access scopes then create separate reports.
I was able to bypass the error by changing the connection mode from direct query to mixed for ACCESS_CONTROL_VIEW.
And I was able to achieve the required functionality i.e. Certain users can access certain pages based on which page they have access to. This is not a straight forward and secure way but it works for now.
Youtube Link -
https://www.youtube.com/watch?v=eHzkn3pSJ4g&t=300s&pp=ygUWcGFnZSBzZWN1cml0eSBwb3dlciBiaQ%3D%3D
It's a great pleasure to know that you've found a solution. To help other members of the community, can you mark your response as a solution? This way others who are experiencing similar problems can find the answer faster.
If you need any help with this, please feel free to let us know.
Thank you for your cooperation and support!
Best wishes
