Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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