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.
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?
Solved! Go to 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.
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.
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
@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
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.