RLS Question with Group

[Anonymous]

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. 

User	Email	Group	Sales Office
1000	ST_1000@abc.com	Office Manager	Bristol
1005	ST_1005@abc.com	Office Manager	Cheshire
1010	ST_1010@abc.com	Office Manager	Essex
1020	PT_1020@abc.com	Prod Manager	Essex
1025	PT_1025@abc.com	Prod Manager	Kent

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

Date	Spending Group	Sales Office	Amount
1/2/2020	20	Bristol	20000
2/2/2020	30	Bristol	10000
3/1/2020	20	Essex	12000
4/2/2020	40	Essex	17000
1/2/2020	100	Essex	19000
1/2/2020	20	Kent	25000
2/2/2020	100	Kent	20000

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

[nandukrishnavs]

@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

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. 

[Anonymous]

Thanks @nandukrishnavs 

Let me  digest through these DAX logic. 

[Anonymous]

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.  

[nandukrishnavs · 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.

[Anonymous]

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