RLS based on user login

[sms2000]

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

[vojtechsima · Accepted solution]

Hello, @sms2000, 

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.

[sms2000]

YESS this is worked perfectly. Thank you @vojtechsima 

[vojtechsima]

@sms2000 Glad, it's working, kudos appreciated. thank you

[PijushRoy]

Hi @sms2000 

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 solved

[sms2000]

i 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 

[Uzi2019]

Hi @sms2000 
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!
Thanks

[sms2000]

i can only make one relationship active between 2 tables , in this case company is active, whichever relationship is active the RLS filters for it . 

[PijushRoy]

Hi @sms2000 

You can keep another inactive relation between two table and use RELATEDTABLE dax as per your requirement.

Thanks