Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

RLS with different tables using LOOKUPVALUE and USERNAME()

Hello, I'm trying to figure out best way how to add RLS feature to my Power BI desktop report. I have few different OLAP cubes, where I can filter either name of SalesMan or name of SalesManager. I manually added table in which i convert name of SalesMan and SalesManager to our domain username. See attached tables

 

Table1: Sales

 

SalesManCategoryAmountSalesManager
SalesMan1Audio12577051,54SalesManager1
SalesMan1Cameras and camcorders215118662,79SalesManager1
SalesMan1Cell phones73948974,63SalesManager1
SalesMan1Computers264928505,00SalesManager1
SalesMan1Music, Movies and Audio Books14402953,90SalesManager1
SalesMan1TV and Video114928807,89SalesManager1
SalesMan2Audio33571565,13SalesManager1
SalesMan2Cameras and camcorders513495945,21SalesManager1
SalesMan2Cell phones181300198,38SalesManager1
SalesMan2Computers638800620,55SalesManager1
SalesMan2Music, Movies and Audio Books32744091,28SalesManager1
SalesMan2TV and Video286158689,34SalesManager1
SalesMan3Audio20928013,43SalesManager2
SalesMan3Cameras and camcorders338052950,15SalesManager2
SalesMan3Cell phones117393301,42SalesManager2
SalesMan3Computers423682371,99SalesManager2
SalesMan3Music, Movies and Audio Books21644305,33SalesManager2
SalesMan3TV and Video182847758,53SalesManager2
SalesMan4Audio84537734,21SalesManager2
SalesMan4Cameras and camcorders1495356215,91SalesManager2
SalesMan4Cell phones519590789,87SalesManager2
SalesMan4Computers1882015643,88SalesManager2
SalesMan4Music, Movies and Audio Books97013355,46SalesManager2
SalesMan4TV and Video776185859,00SalesManager2

 

 

Table2: Users

 

UserLogin
SalesMan1COMPANY\peter
SalesMan2COMPANY\thomas
SalesMan3COMPANY\eve
SalesMan4COMPANY\john
SalesManager1COMPANY\diane
SalesManager2COMPANY\francis

 

I was trying to add RLS role in this format:

[SalesMan] = "LOOKUPVALUE(Users[User],Users[Login],Username())"

 

but it seems it doesn't work.

 

The target is to add RLS feature so SalesMan can only see his results, and SalesManager will see his results and results of his subject SalesMen.

 

I'm quite new to this so I'm probably making some basic mistakes.

 

Thanks for any help

Regards


MV

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

    You can use below formula to achieve your requirement.

     

    RLS Formula:

    if(
    ISERROR(SEARCH("SalesManager", [Role]))=FALSE(),
    [SalesManager]=LOOKUPVALUE(Sheet2[User],Sheet2[Login],USERNAME())
    ,
    [SalesMan]=LOOKUPVALUE(Sheet2[User],Sheet2[Login],USERNAME())
    )

     

    Comment:

    Role=LOOKUPVALUE(Sheet2[User],Sheet2[Login],USERNAME())// check the role of current domain

    IsManager= if(ISERROR(SEARCH("SalesManager", [Role]))=FALSE(),TRUE(),FALSE()) // check if your role is sales manager

     

    Result:

     

     

    Notice: USERNAME function has the different result at desktop side and service side, you should deal with this issue.

    Service:

    Desktop:

     

     

    Regards,

    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

     

    You can use below formula to achieve your requirement.

     

    RLS Formula:

    if(
    ISERROR(SEARCH("SalesManager", [Role]))=FALSE(),
    [SalesManager]=LOOKUPVALUE(Sheet2[User],Sheet2[Login],USERNAME())
    ,
    [SalesMan]=LOOKUPVALUE(Sheet2[User],Sheet2[Login],USERNAME())
    )

     

    Comment:

    Role=LOOKUPVALUE(Sheet2[User],Sheet2[Login],USERNAME())// check the role of current domain

    IsManager= if(ISERROR(SEARCH("SalesManager", [Role]))=FALSE(),TRUE(),FALSE()) // check if your role is sales manager

     

    Result:

     

     

    Notice: USERNAME function has the different result at desktop side and service side, you should deal with this issue.

    Service:

    Desktop:

     

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks a lot, I tried workaround to have different tables for SalesMan and SalesManager, but this looks better. Also my original formula had double apostrophe ("") which was wrong.
      MV

    • jphyatt's avatar
      jphyatt
      Frequent Visitor
      6 years ago

      Anonymous  Are the formulas below "Comment:" measures that are being used to evaluate in the Table filter DAX expression?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Anonymous, Thanks for your answers, your fonction works with userprincipalname?