Forum Discussion
Row Level Security Only on Drillthrough
My solution:
1) Import original dataset, call it XXX
2) In modeling tab, create a new table and set it equal to my original dataset, call it XXX_Secure. (XXX_Secure = XXX)
3) Create a relationship between XXX and XXX_Secure on group name.
4) Configure Row Level Security on the USERPRINCIPALNAME on table Y.
5) Create relationship from table Y to XXX_Security on the group name.
6) Create bar chart off of XXX
7) Create drill through detail page, include all fields you need from XXX
😎 Also add the group name from XXX_Secure to the detail page and minimize the column so it can't be seen.
This will allow all users to see all the data in the bar chart regardless of group name. Every bar in the chart will have a drill through option, but clicking on them will return empty details except for the groups the person viewing the report has access to.
Thanks TCavins.
so a table with user name, upn, and Y or N for drill thru permission?
Not a Y/N for permission but actual value. [email protected], 'Microsoft' can see all records where column x is Microsoft.
In your Power BI report, you set up the security on the report Under Modeling > Manage Roles and create a role, select the table that has your security (Table Y) and a DAX expression to say [Username] = USERPRINCIPALNAME().
You then set up your model to say TableY links to Companies(M*M) and Companies is linked to your dataset(1*M). This way, the security table filters the companies table that then filters your dataset.
In my Microsft example, security table will have bob and Microsoft. The companies table will have a list of distinct companies in your dataset. Your dataset should contain the company column for each record.
In the relationships, you set Table Y filters Company table. Company table filters your dataset.