Forum Discussion

Anonymous

Not applicable

6 years ago

Control Row Level Security Using Excel spreadsheet

Hello, I have created a PBI Report. Now I am exploring options to do RLS. Normal method does work and i have asked users to test. Now moving forward we want to control the RLS using Excel sheet. ...

Icon for Community Support rank

Community Support

6 years ago

hi, Anonymous

You may refer to this blog:

https://radacad.com/dynamic-row-level-security-in-power-bi-with-organizational-hierarchy-and-multiple-positions-in-many-to-many-relationship-part-1

For your case, you could define the role dim table in excel and then import it into power bi report and set refresh for it.

Regards,

Lin

Anonymous
Not applicable
6 years ago
Hello,

I created a excel which will control the RLS with Person name, Email and GEO and Product. That table is called as User_Access in the diagram. Now when i import the model I created a relationship between User_Access and Geo_Hierarchy tables connecting Geo.
Next step i went to manage roles and created a role called accessa and declared [User] = USERPRINCIPALNAME().
Next i published the same to Service and shared the report with a user. When user viewed the report he is getting below error
Can someone tell me where I am going wrong. I checked many forums but nothing helped. If someone can shed some light on where I am going wrong will be helpful.

Note: I did not go to dataset for the report and make any changes. Not sure if that is a step I am missing.
- Anonymous
  Not applicable
  6 years ago
  Has anyone found a way of linking the excel to the dataset access step? Surely there is a way that the excel list can automatically add the users to dataset security?
  - Anonymous
    Not applicable
    6 years ago
    Hi Karthik,
    
    Did you check the bidirectional is active in the relationship level ?