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 m...
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

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?