Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have this data model:
relationship model:
I want to use Row Level security via UserPrincipalName() expression.
I have set up a role for table Employees:
[UserPrincipalName] = userprincipalname()
When I view report as a specific user only two tables are filtered correctly:
But when I tick "Apply security filter in both directions", it filters all the tables:
My question:
sample data here:
https://drive.google.com/file/d/1chwlT_cP3QPvMCC2IH2hxq-TkJhulTLb/view?usp=sharing
As you can see I want to use Power BI and Row Level Security for HR reporting with sensitive information. I want to be sure that employees cannot reach irrelevant data.
Warm regards,
Jakub
Hi @jdusek92 ,
This feature is for direct query and by default, the row-level security filtering uses single-directional filters, regardless of whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filter with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox to apply bi-directional.
Reference:
https://docs.microsoft.com/en-us/power-bi/desktop-bidirectional-filtering
Regards,
Daniel He
Apply security filter in both directions does the trick indeed.
But when I want to have more tables and apply RLS to all of them, I am getting this error message:
How can I work this around ? I want to filter all the tables based on a record in "Employees" table.
Hi @jdusek92 ,
Based on my research, it is due to your relationship will result in a circle filtering that Power BI will not let to create a full circle of relationships between tables due to ambiguity in filtering.
Reference:
Regards,
Daniel He
Hello @v-danhe-msft , thank you for your research. the reference is very helpful, but still:
I understand that I cannot create circular relationships like you show show in the picture.
According to your reference it is recommended to use a Star Schema:
In my opinion I am using this type of schema, I do not have any circular relationships:
Yet I cannot use RLS filtering from EMPLOYEES table to filter BOTH look-up tables (JOBS and CHAIRS). I can be set to filter only one at a time. I need all the related tables to be filtered by RLS
Example pbix is attached here:
https://drive.google.com/open?id=1Y0sYDcSULkRQGpYWv9nfMzaOVzWFrozo
Try view as role:
Maybe this is a limitation of Row Level Security / Roles ?
Regards,
Jakub
@jdusek92 you added 4 seperate tables and viewing them seperately. You dont visualize data like that right.
Just add columns from all 4 tables into one table(visual) and see.....
HI,
I have the same issue. Did you get any solution on this
To be sure, I include a UserPrincipalName column in each table and set up a role filtering for each table. Also I eliminate the tables that cannot be enriched with UPN column (like Job Catalog) - data from such tables must be therefore merged+expanded to main datatables. Otherwise I cannot be sure that unwanted information will be hidden.
Thanks for your quick reply. But when we have a lookup tables it will be difficult to bring the UserName or UserPrincipal to lookup tables. I think I have a different issue, so I have created another thread here
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
83 | |
76 | |
65 |
User | Count |
---|---|
120 | |
112 | |
94 | |
84 | |
75 |