Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ta...
Need Help
  • nandukrishnavs's avatar
    nandukrishnavs
    5 years ago

    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.

     

     

nandukrishnavs's avatar
nandukrishnavs
Community Champion
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

Thanks nandukrishnavs 

 

Let me  digest through these DAX logic. 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      nandukrishnavs
      Community Champion
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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