Forum Discussion
RLS based on user login
i have 2 excels
1. domain, conatins the user, email, dept and company to which it belongs
2. main excel , contains the data for each dept in the companies
i want to define RLS based on the first excel so that when the user logs in they see their respected data.
i have an active relationship between both the excels relating to company , and the dept is inactive.
i have defined user1 in manage roles and added filter email = username(),
this works for the company as it is an active relationship but does not filter dept.
how can i write the RLS for Power BI to check the domain excel and show data assigned only to that user with respect to dept and company.
one user can have multiple acess like same company but coukd ahve access to maybe 2 dept , power bi should consider all this.
DOMAIN EXCEL:
DOMAIN EXCEL
MAIN EXCEL:
MAIN EXCEL
Hello, Anonymous,
in your RLS settings to your RLS table, write [EMAIL]=USERPRINCIPALNAME().
Then create a new column which weill be concatening Company and Dept together (this is optional, you can use as the key any of those withour creating new one), do it in your MAIN Excel and your RLS table, then create relationshisp Many to Many but don't make it both, but RLS filters MAIN EXCEL, key column is the new one.
and then whenever you use Company or Dept, use it from the RLS tabel as dimension.
8 Replies
Hi Anonymous
Please find the video to details - https://www.youtube.com/watch?v=jphj40tBPD8
Please implement RLS in DOMAIN EXCEL table as explained in video. Create relationship with COMPANY column or or other lookup column.
If solve, mark as solvedi have done the same with respect to "COMPANY" that is an active relationship in my model, but i also want power bi to filter the data according to DEPT as well
i have tried to use LOOKUPVALUE but it does not seem to work
how do i make power bi check the dept associated with the user's email and show only that data
Hello, Anonymous,
in your RLS settings to your RLS table, write [EMAIL]=USERPRINCIPALNAME().
Then create a new column which weill be concatening Company and Dept together (this is optional, you can use as the key any of those withour creating new one), do it in your MAIN Excel and your RLS table, then create relationshisp Many to Many but don't make it both, but RLS filters MAIN EXCEL, key column is the new one.
and then whenever you use Company or Dept, use it from the RLS tabel as dimension.
YESS this is worked perfectly. Thank you vojtechsima
Anonymous Glad, it's working, kudos appreciated. thank you
Hi Anonymous
Try to make active relationship between Dept (domain) 1-> Dept(Main) many.
I hope this solve your issue.
If I answered your question.Please give kudos and accept it as a solution!
ThanksHi Anonymous
You can keep another inactive relation between two table and use RELATEDTABLE dax as per your requirement.
Thanks
