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.
I have a core table (Property) and I have another table (Congressional District). Each Congressional District can have 1 or many Properties associated with it and a Property can only have one Congressional District - a classic 1:M, snowflake dimension.
I want the user to be able to see any Property but I need to hide the Congressional District info from a user unless they have permission (role).
I tried using Row-Level Security (RLS) on the Congressional District table, and it works - problem is, since it's related to the Property table in my model, all Properties are also restricted.
Is this sort of thing just not possible using RLS?
Solved! Go to Solution.
Ok, I came up with a solution to this that doesnt involve RLS.
I wanted to combine columns from the base (Property) and snowflake parent (Congressional District) in a single table visual but I want to mask the Congressional District values for users NOT privileged to see them (this is for source system module licensing reasons).
So, I ended up creating measures on the snowflake Dim ("Congressional District") called "XXX_Restricted", where "XXX" is one of fields in the table. The measures look at a custom table that contains the names of the users who should have access to that data.
If the current PBI user (USERPRINCIPALNAME()) is not found in the access table, the measure value will be set to blank, otherwise set to the snowflake value ("Congressional District Member", for example).
The "XXX_Restricted" Measure looks like this:
CD_Member_Restricted = IF ( CONTAINS (V_User_Access_Config,[Data_Object_Name],"<object name>",[Users_ID],USERPRINCIPALNAME()) , SELECTEDVALUE('V_Dim_Congressional_District_Test'[CD_Member]) , BLANK() )
I just need to find a good way to allow the business to manage the User Access list. I'm hoping they can create a table in the source system that I can sync w/ ETL in the Warehouse, giving them the control over its contents.
Kudos to David Eldersveld for stoking the idea:
https://dataveld.com/2018/02/09/masking-measure-values-in-analysis-services-and-power-bi/
Ok, I came up with a solution to this that doesnt involve RLS.
I wanted to combine columns from the base (Property) and snowflake parent (Congressional District) in a single table visual but I want to mask the Congressional District values for users NOT privileged to see them (this is for source system module licensing reasons).
So, I ended up creating measures on the snowflake Dim ("Congressional District") called "XXX_Restricted", where "XXX" is one of fields in the table. The measures look at a custom table that contains the names of the users who should have access to that data.
If the current PBI user (USERPRINCIPALNAME()) is not found in the access table, the measure value will be set to blank, otherwise set to the snowflake value ("Congressional District Member", for example).
The "XXX_Restricted" Measure looks like this:
CD_Member_Restricted = IF ( CONTAINS (V_User_Access_Config,[Data_Object_Name],"<object name>",[Users_ID],USERPRINCIPALNAME()) , SELECTEDVALUE('V_Dim_Congressional_District_Test'[CD_Member]) , BLANK() )
I just need to find a good way to allow the business to manage the User Access list. I'm hoping they can create a table in the source system that I can sync w/ ETL in the Warehouse, giving them the control over its contents.
Kudos to David Eldersveld for stoking the idea:
https://dataveld.com/2018/02/09/masking-measure-values-in-analysis-services-and-power-bi/
Hi @ThomVF ,
Based on my test, it is not supported yet currently.
You can come up a new idea about that and add your comments there to improve Power BI and make this feature coming sooner.
https://ideas.powerbi.com/forums/265200-power-bi-ideas
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |