Dynamic Row Level Security - Single Role for all the User

[milankRaj]

Hi ,

We have two tables : One is a userrole table and another is Summary table. I want to implement dynamic RLS in this report with the following condition - If a Area level user  login to the report with his login, he should be able to see all the data corresponding to that Area. Similarly for Region and country users. How can we implement this using single dynamic role?

Summaryuserroles

[nandukrishnavs · Accepted solution]

Hi Milan,

1. create a calculated column called Role in userroles table.

Role = 
VAR areaname = userroles[Area]
VAR regionname = userroles[Region]
VAR countryname = userroles[Country]
VAR result =
    IF (
        countryname <> BLANK (),
        "Country",
        IF ( regionname <> BLANK (), "Region", IF ( areaname <> BLANK (), "Area", "" ) )
    )
RETURN
    result

2. Create a Role called AllRoles in Manage Roles window.

Apply the below DAX filter in the summary table.

VAR role =
CALCULATE (
    SELECTEDVALUE ( userroles[Role] ),
    userroles[Username] = USERPRINCIPALNAME ()
)


VAR a =
CALCULATE (
    SELECTEDVALUE ( userroles[Area] ),
    userroles[Username] = USERPRINCIPALNAME ()
)


VAR r =
CALCULATE (
    SELECTEDVALUE ( userroles[Region] ),
    userroles[Username] = USERPRINCIPALNAME ()
)


VAR c =
CALCULATE (
    SELECTEDVALUE ( userroles[Country] ),
    userroles[Username] = USERPRINCIPALNAME ()
)


RETURN (
IF(role="Area",[AccountHdqArea]= a,
   IF(role="Region",[AccountHdqRegion] = r,
       IF(role="Country",[AccountHdqCountryName] = c)
      )
   )
)

Apply the below DAX filter in the userroles table.

[Username] =USERPRINCIPALNAME()

Now you can map all the users in AllRoles in the Power BI service.

Try out this solution and let me know if it is working for you.

[SimonGotte]

Thank you for a good solution @nandukrishnavs 

I have tried to use this in my RLS-setup and have run into a problem. In my company, a user can have access to several different countries not in the same area. I have approached this by having new lines for each country.

When I try to use your approach I run into the problem that SELECTEDVALUE will only accept a single value, or return a blank.

Do you have any suggestions for when there is a possibility of an array of countries, that does not qualify as an area, that need to be accessed through the RLS?

Many thanks

Simon

[nandukrishnavs]

@SimonGotte  - You can try VALUES () 

PLease refer below code sample. 

//getting the admin list

VAR adminUser = values(Admin[Admin])

//if the loged in user is available in admin list, no filter condition is applying

VAR result = 
IF ( USERPRINCIPALNAME() IN adminUser,TRUE(),
       [Counselor Email Address] = USERPRINCIPALNAME() 
)

RETURN result

Regards,

Nandu Krishna