Forum Discussion
Row level security to hide sensitive data in facts table
- 5 years ago
If your users are editors or contributors then they will be able to access it. There is no column level security in Power BI.
I see you are relying on many-to-many relationships and this could help us with an indirect solution depending on your data size.
If your M_table is not that big in size then you may duplicate it in Power Query after removing the values from your sales amount... make it zeros and add a new column lets say SecurityApplied=1.
Add the same column to your original table with 0 value (SecurityApplied=0) then append the new table to the original one.
In your security roles you have to filter the appended table for all users to have either SecurityApplied either 1 or 0 to avoid duplicating other values for users with higher privileges.
- 5 years ago
I agree with Mohammad_Refaei here: RLS and OLS is meant to be created in PBI Desktop (well, OLS needs Tabular Editor, but it's doable) and applied in PBI Service. WIth OLS it is now possible to hide columns. Second, I don't see a need to implement RLS at the measure level - generally you want to keep your measures clean. I would recommend starting with the link below and adjusting your approach:
Row Level Security Configuration in Power BI Desktop - RADACADReza also has a great book dedicated specifically to RLS:
https://www.amazon.com/Row-Level-Security-Power-BI-different-ebook/dp/B082SFR2J4
If your users are editors or contributors then they will be able to access it. There is no column level security in Power BI.
I see you are relying on many-to-many relationships and this could help us with an indirect solution depending on your data size.
If your M_table is not that big in size then you may duplicate it in Power Query after removing the values from your sales amount... make it zeros and add a new column lets say SecurityApplied=1.
Add the same column to your original table with 0 value (SecurityApplied=0) then append the new table to the original one.
In your security roles you have to filter the appended table for all users to have either SecurityApplied either 1 or 0 to avoid duplicating other values for users with higher privileges.
I agree with Mohammad_Refaei here: RLS and OLS is meant to be created in PBI Desktop (well, OLS needs Tabular Editor, but it's doable) and applied in PBI Service. WIth OLS it is now possible to hide columns. Second, I don't see a need to implement RLS at the measure level - generally you want to keep your measures clean. I would recommend starting with the link below and adjusting your approach:
Row Level Security Configuration in Power BI Desktop - RADACAD
Reza also has a great book dedicated specifically to RLS:
https://www.amazon.com/Row-Level-Security-Power-BI-different-ebook/dp/B082SFR2J4