Forum Discussion

milankRaj's avatar
milankRaj
Advocate I
7 years ago
Solved

Dynamic Row Level Security - Single Role for all the User

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 re...
Data Modeling
  • nandukrishnavs's avatar
    nandukrishnavs
    7 years ago

    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.

nandukrishnavs's avatar
nandukrishnavs
Community Champion
7 years ago

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's avatar
    SimonGotte
    Regular Visitor
    6 years ago

    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's avatar
      nandukrishnavs
      Community Champion
      6 years ago

      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