Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

RLS Question with Group

I am trying to use the RLS for a report and wanted guidance. 

 

The report is to provide the details of the Spending amounts for the Sales Offices and the specific spending group. 

 

The below table is frrm  AD data with security groups tied to the different users. 

UserEmailGroupSales Office
1000ST_1000@abc.comOffice ManagerBristol
1005ST_1005@abc.comOffice ManagerCheshire
1010ST_1010@abc.comOffice ManagerEssex
1020PT_1020@abc.comProd ManagerEssex
1025PT_1025@abc.comProd ManagerKent

 

The fact table has below data with spending groups and the amount 

 

DateSpending GroupSales OfficeAmount
1/2/202020Bristol20000
2/2/202030Bristol10000
3/1/202020Essex12000
4/2/202040Essex17000
1/2/2020100Essex19000
1/2/202020Kent25000
2/2/2020100Kent20000

 

The users tied to office manager group should see only the data related to their office and spending group NOT IN 100. 

 

In addition, there are management users who would need to view all the data. I have not added them to the AD groups as there are only few users. 

How can the rule be defined to implement this. I am trying to look at the radacad examples and trying to learn in parallel , but wanted to ask this question to the experts . 

 

Pbix file 

https://drive.google.com/file/d/1WOksrHS8FT0H4NkL-Lej4r92Uxhz3UZl/view?usp=sharing

1 ACCEPTED SOLUTION

@Anonymous 

 

var _admin=DISTINCT('Admin'[Email])

Getting the list of admin emails and assign it to _admin variable

 

var _x = CALCULATETABLE(Division,Division[Email]=USERPRINCIPALNAME())

Filtering the Division table based on login user and assign that into _x variable

 

var _office = SELECTCOLUMNS(_x,"Office",[Sales Office])

Creating a new table called _office. It will contain a single column "Office" which is derived from _x table. ie login user's office.

 

var _group = SELECTCOLUMNS(_x,"Group",[Group])

Creating a new table called _group. It will contain a single column "Group" which is derived from _x table. ie login user's group.

 

var _OfficeManagerRole = AND([Sales Office] IN _office,[Spending Group]<>100)

Specifying a filter condition, Office Manager Role

 

var _ProdManagerRole = [Sales Office] IN _office

Specifying a filter condition, Product Manager Role

 

var _finalFilter =SWITCH(TRUE(), 
USERPRINCIPALNAME() IN _admin, TRUE(),
"Office Manager" IN _group, _OfficeManagerRole,
"Prod Manager" IN _group, _ProdManagerRole)
return _finalFilter

Deciding the user role based on the login user.

 

 


Regards,
Nandu Krishna

View solution in original post

5 REPLIES 5
nandukrishnavs
Super User
Super User

@Anonymous 

 

Try below DAX in the Manage Roles window.

var _admin=DISTINCT('Admin'[Email])

var _x = CALCULATETABLE(Division,Division[Email]=USERPRINCIPALNAME())

var _office = SELECTCOLUMNS(_x,"Office",[Sales Office])

var _group = SELECTCOLUMNS(_x,"Group",[Group])

var _OfficeManagerRole = AND([Sales Office] IN _office,[Spending Group]<>100)

var _ProdManagerRole = [Sales Office] IN _office

var _finalFilter =SWITCH(TRUE(), 
USERPRINCIPALNAME() IN _admin, TRUE(),
"Office Manager" IN _group, _OfficeManagerRole,
"Prod Manager" IN _group, _ProdManagerRole)
return _finalFilter

nandukrishnavs_0-1601620626430.png

I have attached the PBIX file for your reference.

Your actual requirements might be different. Since the management users are missing in the table, I have created a calculated table as Admin. Please modify the logic based on your requirement. 

 


Regards,
Nandu Krishna

Anonymous
Not applicable

Thanks @nandukrishnavs 

 

Let me  digest through these DAX logic. 

 

 

 

Anonymous
Not applicable

Thanks @nandukrishnavs for provoding the steps. 

 

Trying to understand the steps you did in this Dax. 

 

Creating the virtual table with  _X.  But , the selectcolumn is that really to filter the specific condition for Office and Group ? 

So if i have additional conditions that i need to filter on, the approach will be to get the virtual table and filter with selectcolumn? 

 

Getting myself familiarised with these functions. Thanks again for the help.  

 

 

 

 

@Anonymous 

 

var _admin=DISTINCT('Admin'[Email])

Getting the list of admin emails and assign it to _admin variable

 

var _x = CALCULATETABLE(Division,Division[Email]=USERPRINCIPALNAME())

Filtering the Division table based on login user and assign that into _x variable

 

var _office = SELECTCOLUMNS(_x,"Office",[Sales Office])

Creating a new table called _office. It will contain a single column "Office" which is derived from _x table. ie login user's office.

 

var _group = SELECTCOLUMNS(_x,"Group",[Group])

Creating a new table called _group. It will contain a single column "Group" which is derived from _x table. ie login user's group.

 

var _OfficeManagerRole = AND([Sales Office] IN _office,[Spending Group]<>100)

Specifying a filter condition, Office Manager Role

 

var _ProdManagerRole = [Sales Office] IN _office

Specifying a filter condition, Product Manager Role

 

var _finalFilter =SWITCH(TRUE(), 
USERPRINCIPALNAME() IN _admin, TRUE(),
"Office Manager" IN _group, _OfficeManagerRole,
"Prod Manager" IN _group, _ProdManagerRole)
return _finalFilter

Deciding the user role based on the login user.

 

 


Regards,
Nandu Krishna

Anonymous
Not applicable

Thanks @nandukrishnavs for guiding with those detailed steps. 

 

I wanted to expand the condition with 2 more filters and added 2 tables for it. Played around with my condition and i feel i am missing something in setting this condition. 

 

The restriction is for Spending group to 20 & 30 for Office manager . Under the 20 Spending Group, all data with any section code can be shown . But on 30, only section code with CA should be shown. 

The conditions added is below , 

var _secsg = CALCULATETABLE('Security SG','Security SG'[Group]IN _group)

var _sg = SELECTCOLUMNS(_secsg,"Sp Grp",[Spend Group])

var _secscode = CALCULATETABLE('Security section code','Security section code'[Group]IN _group)

var _sc = SELECTCOLUMNS(_secscode,"Sec Code",[Section Code])

var _OfficeManagerRole =
[Sales Office] IN _office &&
[Spending Group] IN _sg || 

[Sales Office] IN _office &&
[Spending Group] IN _sg &&

[Section Code] IN _sc

 

https://drive.google.com/file/d/1ahniIS4uGVfobTVvQp7E0J9GPJseYBfM/view?usp=sharing

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors