RLS with different tables using LOOKUPVALUE and USERNAME()

[Anonymous]

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

SalesMan	Category	Amount	SalesManager
SalesMan1	Audio	12577051,54	SalesManager1
SalesMan1	Cameras and camcorders	215118662,79	SalesManager1
SalesMan1	Cell phones	73948974,63	SalesManager1
SalesMan1	Computers	264928505,00	SalesManager1
SalesMan1	Music, Movies and Audio Books	14402953,90	SalesManager1
SalesMan1	TV and Video	114928807,89	SalesManager1
SalesMan2	Audio	33571565,13	SalesManager1
SalesMan2	Cameras and camcorders	513495945,21	SalesManager1
SalesMan2	Cell phones	181300198,38	SalesManager1
SalesMan2	Computers	638800620,55	SalesManager1
SalesMan2	Music, Movies and Audio Books	32744091,28	SalesManager1
SalesMan2	TV and Video	286158689,34	SalesManager1
SalesMan3	Audio	20928013,43	SalesManager2
SalesMan3	Cameras and camcorders	338052950,15	SalesManager2
SalesMan3	Cell phones	117393301,42	SalesManager2
SalesMan3	Computers	423682371,99	SalesManager2
SalesMan3	Music, Movies and Audio Books	21644305,33	SalesManager2
SalesMan3	TV and Video	182847758,53	SalesManager2
SalesMan4	Audio	84537734,21	SalesManager2
SalesMan4	Cameras and camcorders	1495356215,91	SalesManager2
SalesMan4	Cell phones	519590789,87	SalesManager2
SalesMan4	Computers	1882015643,88	SalesManager2
SalesMan4	Music, Movies and Audio Books	97013355,46	SalesManager2
SalesMan4	TV and Video	776185859,00	SalesManager2

Table2: Users

User	Login
SalesMan1	COMPANY\peter
SalesMan2	COMPANY\thomas
SalesMan3	COMPANY\eve
SalesMan4	COMPANY\john
SalesManager1	COMPANY\diane
SalesManager2	COMPANY\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

[Anonymous · Accepted solution]

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]

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

[jphyatt]

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

[Anonymous]

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