Need help creating Row Level Security based on the Department Name in the Table

[Jadegirlify]

HR Table (HR Report)

Department	Employee ID	Employee	email
Acct	111	A Smith	Asmith@gmail.com
Acct	222	B Daks	BDaks@gmail.com
Acct	333	C Mark	CMark@gmail.com
Acct	444	D Tim	DTim@gmail.com
Acct	555	E Sam	ESam@gmail.com
Acct	666	F Jack	FJack@gmail.com
Data	150	G Kim	GKim@gmail.com
Data	250	H Tom	HTom@gmail.com
Data	350	I Boy	IBoy@gmail.com
Data	450	J May	JMay@gmail.com
Data	550	K Leo	KLeo@gmail.com
Data	650	L Pam	LPam@gmail.com
Data	750	M Luke	MLuke@gmail.com
IT	100	N Kobe	NKobe@gmail.com
IT	200	O Will	OWill@gmail.com
IT	300	P Heal	PHeal@gmail.com
IT	400	Q Taz	QTaz@gmail.com
IT	500	R Sun	RSun@gmail.com
IT	600	S Craig	SCraig@gmail.com
IT	700	T Corey	TCorey@gmail.com

Medical Table (Medical Report)

Department	Employee ID	Employee	email	Medical
Acct	111	A Smith	Asmith@gmail.com	Respiratory
Acct	111	A Smith	Asmith@gmail.com	TB
Acct	111	A Smith	Asmith@gmail.com	Chol
Acct	222	B Daks	BDaks@gmail.com	TB
Acct	333	C Mark	CMark@gmail.com	Chol
Acct	444	D Tim	DTim@gmail.com	Typ
Acct	555	E Sam	ESam@gmail.com	Chol
Acct	666	F Jack	FJack@gmail.com	TB
Acct	666	F Jack	FJack@gmail.com	Chol
Data	150	G Kim	GKim@gmail.com	Chol
Data	250	H Tom	HTom@gmail.com	Respiratory
Data	350	I Boy	IBoy@gmail.com	TB
Data	450	J May	JMay@gmail.com	Chol
Data	550	K Leo	KLeo@gmail.com	Chol
Data	650	L Pam	LPam@gmail.com	Typ
Data	750	M Luke	MLuke@gmail.com	Respiratory
Data	750	M Luke	MLuke@gmail.com	TB
Data	750	M Luke	MLuke@gmail.com	Typ
Data	750	M Luke	MLuke@gmail.com	Chol
IT	100	N Kobe	NKobe@gmail.com	TB
IT	200	O Will	OWill@gmail.com	TB
IT	300	P Heal	PHeal@gmail.com	Typ
IT	400	Q Taz	QTaz@gmail.com	Typ
IT	500	R Sun	RSun@gmail.com	Typ
IT	600	S Craig	SCraig@gmail.com	Chol
IT	700	T Corey	TCorey@gmail.com	Chol

Mask Table (Mask Report)

Department	Employee ID	Employee	email	Mask
Acct	111	A Smith	Asmith@gmail.com	N95
Acct	111	A Smith	Asmith@gmail.com	Surgical
Acct	111	A Smith	Asmith@gmail.com	Basic
Acct	222	B Daks	BDaks@gmail.com	N95
Acct	333	C Mark	CMark@gmail.com	Basic
Acct	444	D Tim	DTim@gmail.com	Basic
Acct	555	E Sam	ESam@gmail.com	Surgical
Acct	666	F Jack	FJack@gmail.com	Basic
Acct	666	F Jack	FJack@gmail.com	Surgical
Data	150	G Kim	GKim@gmail.com	Surgical
Data	250	H Tom	HTom@gmail.com	N95
Data	350	I Boy	IBoy@gmail.com	Surgical
Data	450	J May	JMay@gmail.com	Surgical
Data	550	K Leo	KLeo@gmail.com	Surgical
Data	650	L Pam	LPam@gmail.com	Surgical
Data	750	M Luke	MLuke@gmail.com	Basic
Data	750	M Luke	MLuke@gmail.com	Surgical
Data	750	M Luke	MLuke@gmail.com	N95
IT	100	N Kobe	NKobe@gmail.com	Basic
IT	200	O Will	OWill@gmail.com	N95
IT	300	P Heal	PHeal@gmail.com	N95
IT	400	Q Taz	QTaz@gmail.com	Surgical
IT	500	R Sun	RSun@gmail.com	Basic
IT	600	S Craig	SCraig@gmail.com	Basic
IT	700	T Corey	TCorey@gmail.com	Basic

• All Tables were imported from Dataverse
• How can I set up row level security in powerBi so that each employee can only see data from their respective department in the table. These employee are not members in the workspace. The report will only be published and shared via powerbi online link
  1. When “A Smith” in Acct department open/views the HR Report page, he must only see the following record:

  Department	Employee ID	Employee	email
  Acct	111	A Smith	Asmith@gmail.com
  Acct	222	B Daks	BDaks@gmail.com
  Acct	333	C Mark	CMark@gmail.com
  Acct	444	D Tim	DTim@gmail.com
  Acct	555	E Sam	ESam@gmail.com
  Acct	666	F Jack	FJack@gmail.com

[MFelix · Accepted solution]

Hi @Jadegirlify ,

For this you would need to have a formula in the department column that would get the department for the current login something similar to:

var selecteduserdepartment = MAXX(FILTER(Users, Users[email] = USERPRINCIPALNAME()), Users[Department])

RETURN
 Users[Department] = selecteduserdepartment

This will allow you to do the filter you need:

This need to be applied to the user table:

My only question is what do you mean when you say that "These employee are not members in the workspace. The report will only be published and shared via powerbi online link"?

What is the Power BI Online Link? Is it the Share to Web link? that this is not a secure link and RLS will not work on top of it?

RLS only works within the service and can be trough a workspace or giving acces to an app.