Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @mvyskala,
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
Hi @mvyskala,
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
@mvyskala, Thanks for your answers, your fonction works with userprincipalname?
@v-shex-msft Are the formulas below "Comment:" measures that are being used to evaluate in the Table filter DAX expression?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |