Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
perezco
Advocate III
Advocate III

Dynamic RLS with masked user and partner

I am testing out row level security in my pbix file and it is not working as expected.

Looking  in use a  “Single Role for all the User”

The model has three tables : One is Fact table that has aggregate data,  second Dim -person is just general information of the agent   and third table is RLS security.

Current active relation between tables

Fact table    (user_id)  <--- dim person )person_id)
Dim person (badge_id) <---> RLSE security (badge_id)  M2M.

Reference: https://radacad.com/find-the-data-value-using-lookupvalue-dax-function-in-power-bi-simple-and-useful

 

MODELmodelmodel

Type of Data from  RLS security table

type of datatype of data

 

partner

role partnerrole partner

example dataexample data

  • "Role Partner column" is designed to limit outsources' access to their data while allowing local users to see all data.
    The "Masked column" is a component of the "Masked Protected flag," and it serves to identify the user who was impacted by the masking. This allow only managers from the same location to view their agents, with the agent's name being Masked= 1 from managers in other locations.
  • Under table 'RLS Security' to have the parent/child relation>> created column
    Relationship = PATH('RLS Security'[USER_BDGE_NBR],'RLS Security'[MGR_BDGE_NBR])
  • Then, under ‘manage roles’ > ‘Manage security roles’
    • Created the role called : RLS Security Mask Partner
    • Then, went to the Table>> ‘RLS Security’
    • And under dax editor added the following
    • But this logic is failing(no error message 😞

 

 

VAR upn = SUBSTITUTE(SUBSTITUTE(USERPRINCIPALNAME(), "_","."),"@mall","@mell")
VAR bdg = MAXX(FILTER('RLS Security‘, 'RLS Security ‘[user_Email_ID] = upn),'RLS Security ‘[user_BDGE_NBR])
VAR condition1= PATHCONTAINS('RLS Security ‘[Relationship], bdg)
VAR condition2 = 'RLS Security'[Masked]
VAR condition3= 'RLS Security' [role partner]

Return
( NOT(condition1) && NOT(condition2) && NOT(condition3) )
||
(NOT(condition1) && NOT(condition2) && condition3)
||
(NOT(condition1) && condition2 && NOT(condition3) )
||
(NOT(condition1) && condition2 && condition3)
||
(condition1 && NOT(condition2) && NOT(condition3) )
||
(condition1 && NOT(condition2) && condition3)
||
(condition1 && condition2 && NOT(condition3) )
||
(condition1 && condition2 && condition3)

 

 





3 REPLIES 3
perezco
Advocate III
Advocate III

In this moment I can get  from the 'all_in_one RSL' >> user role_partner =  0 or 1   see all .

 

'existing code' 

 

VAR upn = SUBSTITUTE(SUBSTITUTE(USERPRINCIPALNAME(), "_","."),"@mall","@mell")
VAR bdg = MAXX(FILTER('RLS Security', 'RLS Security'[user_Email_ID] = upn),'RLS Security ‘[user_BDGE_NBR])
VAR condition1= PATHCONTAINS('RLS Security'[Relationship], bdg)
VAR condition2 = 'RLS Security'[Masked]
VAR condition3= 'RLS Security' [role partner]
Return
// 1,0,1
(condition1 && NOT(condition2) && condition3)
||
//1,1,1
(condition1 && condition2 && condition3)
||
//0,1,0
(NOT(condition1) && condition2 && NOT(condition3) )
||
//0,0,1
(NOT(condition1) && NOT(condition2) && condition3)

 

 


But I am  looking

  • user role_partner =  0    see Everything.

user role_partner =  1  see only their data



Conceptual -I 'm not sure this can be done

 

IF ('RLS Security'[Role Partner] = 0, 
'existing code'...,
'only show own data')

 

 

lbendlin
Super User
Super User

Your Return statement will always evaluate to True. Please review it.


Thank you. This means that I have to mantain what I need.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors