Dynamic RLS from a SSAS Tabular Model Data Source

Steve_welman · ‎04-17-2020

Hi All

I have spent may too much trying to figure this out and keep getting stumped and would appreciate any help I can get in getting the RLS working.

My user table, called Query look like the one below but with over 10000 rows.

Row No	Pathd	EmployeeID	EmployeeHRContactID	UPN2	FullName	PthCntns	GUID	GUIDUPN
1	124072\|288975\|160261\|193612\|251905	251905	193612	sjasper001	Nola Viau	FALSE	nviau001	nviau001@userdomain.com
2	124072\|288975\|160261\|193612\|251905\|120345	120345	251905	sjasper001	Bernadine Gulledge	FALSE	bgulledge001	bgulledge001@userdomain.com
3	124072\|288975\|160261\|193612\|251905\|120345\|116884	116884	120345	sjasper001	Joey Warne	FALSE	jwarne001	jwarne001@userdomain.com
4	124072\|288975\|160261\|193612\|251905\|120345\|127392	127392	120345	sjasper001	Keva Dow	FALSE	kdow001	kdow001@userdomain.com
5	124072\|288975\|160261\|193612\|251905\|120345\|138799	138799	120345	sjasper001	Sophia Corsi	FALSE	scorsi001	scorsi001@userdomain.com
6	124072\|288975\|160261\|193612\|251905\|120345\|1395908	1395908	120345	sjasper001	Dalton Mcmillan	FALSE	dmcmillan001	dmcmillan001@userdomain.com
7	124072\|288975\|160261\|193612\|251905\|120345\|2262522	2262522	120345	sjasper001	Karmen Sharer	FALSE	ksharer001	ksharer001@userdomain.com
8	124072\|288975\|160261\|193612\|251905\|120345\|2476227	2476227	120345	sjasper001	Isidra Wurst	FALSE	iwurst001	iwurst001@userdomain.com
9	124072\|288975\|160261\|193612\|251905\|120345\|252840	252840	120345	sjasper001	Berniece Fellers	FALSE	bfellers001	bfellers001@userdomain.com
10	124072\|288975\|160261\|193612\|251905\|120345\|276943	276943	120345	sjasper001	Migdalia Elliott	FALSE	melliott001	melliott001@userdomain.com
11	124072\|288975\|160261\|193612\|251905\|120345\|314882	314882	120345	sjasper001	Normand Zertuche	FALSE	nzertuche001	nzertuche001@userdomain.com
12	124072\|288975\|160261\|193612\|251905\|120345\|473396	473396	120345	sjasper001	Flossie Bloyd	FALSE	fbloyd001	fbloyd001@userdomain.com
13	124072\|288975\|160261\|193612\|251905\|155682	155682	251905	sjasper001	Alita Blackwood	FALSE	ablackwoo001	ablackwoo001@userdomain.com
14	124072\|288975\|160261\|193612\|251905\|192923	192923	251905	sjasper001	Siobhan Jasper	TRUE	sjasper001	sjasper001@userdomain.com
15	124072\|288975\|160261\|193612\|251905\|192923\|118052	118052	192923	sjasper001	Dannielle Hamman	FALSE	dhamman001	dhamman001@userdomain.com
16	124072\|288975\|160261\|193612\|251905\|192923\|137052	137052	192923	sjasper001	Alona Platter	FALSE	aplatter001	aplatter001@userdomain.com
17	124072\|288975\|160261\|193612\|251905\|192923\|194383	194383	192923	sjasper001	Wallace Brazeal	FALSE	wbrazeal001	wbrazeal001@userdomain.com
18	124072\|288975\|160261\|193612\|251905\|192923\|201697	201697	192923	sjasper001	Garnet Nilges	FALSE	gnilges001	gnilges001@userdomain.com
19	124072\|288975\|160261\|193612\|251905\|192923\|216452	216452	192923	sjasper001	Karrie Seyler	FALSE	kseyler001	kseyler001@userdomain.com
20	124072\|288975\|160261\|193612\|251905\|192923\|221116	221116	192923	sjasper001	Sofia Andersen	FALSE	sandersen001	sandersen001@userdomain.com
21	124072\|288975\|160261\|193612\|251905\|192923\|279296	279296	192923	sjasper001	Steven Feathers	FALSE	sfeathers001	sfeathers001@userdomain.com
22	124072\|288975\|160261\|193612\|251905\|192923\|3830032	3830032	192923	sjasper001	Polly Teel	FALSE	pteel001	pteel001@userdomain.com
23	124072\|288975\|160261\|193612\|251905\|254767	254767	251905	sjasper001	Tabitha Mullinix	FALSE	tmullinix001	tmullinix001@userdomain.com
24	124072\|288975\|160261\|193612\|251905\|936324	936324	251905	sjasper001	Janae Pechacek	FALSE	jpechacek001	jpechacek001@userdomain.com
25	124072\|288975\|160261\|193612\|251905\|936324\|167089	167089	936324	sjasper001	Ariel Tempel	FALSE	atempel001	atempel001@userdomain.com
26	124072\|288975\|160261\|193612\|251905\|936324\|210530	210530	936324	sjasper001	Yi Mumford	FALSE	ymumford001	ymumford001@userdomain.com

The Pathd column is defined as PATH(Query[EmployeeID],Query[EmployeeHRContactID])

The UPN2 is an extract of the USERPRINCIPALNAME excluding the domain of the user access the model in the service

The GUIDUPN is a combination of the GUID and the domain.

The PthCntns is TRUE where the incoming USERPRINCIPALName equals the GUIDUPN.

What I need it to display is all the users who report to a user. If the user is Siobhan Jasper (row 14), then I would need to see all the records where Siobhan's EmployeeID appears in the PathD (rows 14 to 22)

The rule on the Role in SSAS is defined as:

=if(
PATHCONTAINS(
'Query'[Pathd],
MAXX(
FILTER(
Query,'Query'[GUIDUPN]=USERPRINCIPALNAME()),
'Query'[EmployeeID])),
'Query'[Display]=1,
'Query'[Display]=0
)

My Gateway will parse the incoming USERPRINCIPALNAME as sjasper001@userdomain.com if Siobhan Jasper is the user viewing the dashboard.

I hope I have explaned this well enough for everyone to understand and hopefully be able to assist me?

Kind Regards

Greg_Deckler · ‎04-17-2020

http://aboutpowerbi.com/index.php/2020/01/04/hierarchical-dynamic-row-level-security-rls/

See if that helps.

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Steve_welman · ‎04-17-2020

Thanks for this, it works perfectly, as long as SSAS is not involved.

Maybe I should of mentioned that I am running as a direct query model.

v-kelly-msft · ‎04-20-2020

Hi @Steve_welman ，

If I didnt misunderstand wrong,you wanna set a RLS with a SSAS model,Right?

Here is a limitation about RLS that is ,only Import and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premises model.

Here the link for the reference.

https://docs.microsoft.com/en-us/power-bi/service-admin-rls

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!