cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
milankRaj New Member
New Member

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 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?

Summary.PNGSummaryuserroles.PNGuserroles

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
nandukrishnavs Frequent Visitor
Frequent Visitor

Re: Dynamic Row Level Security - Single Role for all the User

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.

1 REPLY 1
Highlighted
nandukrishnavs Frequent Visitor
Frequent Visitor

Re: Dynamic Row Level Security - Single Role for all the User

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.