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.