Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Steve_welman
Regular Visitor

Dynamic RLS from a SSAS Tabular Model Data Source

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 NoPathdEmployeeIDEmployeeHRContactIDUPN2FullNamePthCntnsGUIDGUIDUPN
1124072|288975|160261|193612|251905251905193612sjasper001Nola ViauFALSEnviau001nviau001@userdomain.com
2124072|288975|160261|193612|251905|120345120345251905sjasper001Bernadine GulledgeFALSEbgulledge001bgulledge001@userdomain.com
3124072|288975|160261|193612|251905|120345|116884116884120345sjasper001Joey WarneFALSEjwarne001jwarne001@userdomain.com
4124072|288975|160261|193612|251905|120345|127392127392120345sjasper001Keva DowFALSEkdow001kdow001@userdomain.com
5124072|288975|160261|193612|251905|120345|138799138799120345sjasper001Sophia CorsiFALSEscorsi001scorsi001@userdomain.com
6124072|288975|160261|193612|251905|120345|13959081395908120345sjasper001Dalton McmillanFALSEdmcmillan001dmcmillan001@userdomain.com
7124072|288975|160261|193612|251905|120345|22625222262522120345sjasper001Karmen SharerFALSEksharer001ksharer001@userdomain.com
8124072|288975|160261|193612|251905|120345|24762272476227120345sjasper001Isidra WurstFALSEiwurst001iwurst001@userdomain.com
9124072|288975|160261|193612|251905|120345|252840252840120345sjasper001Berniece FellersFALSEbfellers001bfellers001@userdomain.com
10124072|288975|160261|193612|251905|120345|276943276943120345sjasper001Migdalia ElliottFALSEmelliott001melliott001@userdomain.com
11124072|288975|160261|193612|251905|120345|314882314882120345sjasper001Normand ZertucheFALSEnzertuche001nzertuche001@userdomain.com
12124072|288975|160261|193612|251905|120345|473396473396120345sjasper001Flossie BloydFALSEfbloyd001fbloyd001@userdomain.com
13124072|288975|160261|193612|251905|155682155682251905sjasper001Alita BlackwoodFALSEablackwoo001ablackwoo001@userdomain.com
14124072|288975|160261|193612|251905|192923192923251905sjasper001Siobhan JasperTRUEsjasper001sjasper001@userdomain.com
15124072|288975|160261|193612|251905|192923|118052118052192923sjasper001Dannielle HammanFALSEdhamman001dhamman001@userdomain.com
16124072|288975|160261|193612|251905|192923|137052137052192923sjasper001Alona PlatterFALSEaplatter001aplatter001@userdomain.com
17124072|288975|160261|193612|251905|192923|194383194383192923sjasper001Wallace BrazealFALSEwbrazeal001wbrazeal001@userdomain.com
18124072|288975|160261|193612|251905|192923|201697201697192923sjasper001Garnet NilgesFALSEgnilges001gnilges001@userdomain.com
19124072|288975|160261|193612|251905|192923|216452216452192923sjasper001Karrie SeylerFALSEkseyler001kseyler001@userdomain.com
20124072|288975|160261|193612|251905|192923|221116221116192923sjasper001Sofia AndersenFALSEsandersen001sandersen001@userdomain.com
21124072|288975|160261|193612|251905|192923|279296279296192923sjasper001Steven FeathersFALSEsfeathers001sfeathers001@userdomain.com
22124072|288975|160261|193612|251905|192923|38300323830032192923sjasper001Polly TeelFALSEpteel001pteel001@userdomain.com
23124072|288975|160261|193612|251905|254767254767251905sjasper001Tabitha MullinixFALSEtmullinix001tmullinix001@userdomain.com
24124072|288975|160261|193612|251905|936324936324251905sjasper001Janae PechacekFALSEjpechacek001jpechacek001@userdomain.com
25124072|288975|160261|193612|251905|936324|167089167089936324sjasper001Ariel TempelFALSEatempel001atempel001@userdomain.com
26124072|288975|160261|193612|251905|936324|210530210530936324sjasper001Yi MumfordFALSEymumford001ymumford001@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

 

3 REPLIES 3
Greg_Deckler
Community Champion
Community Champion

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...

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.

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.