Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Power BI RLS with SSAS 16 Tabular model. Need to have a 1:many filter

Hi all, we're in the process of moving an Import based Power BI model to a LiveConnect to SSAS Tabular Model and i'm having issues with the DAX to replicate our user security from the Import Model.


I've looked at some of the on-line Dynamic RLS articles and these seem to only work with a 1:1 relationship due to the use of the Lookup function.   If more than one row is returned it throws an error.


I'm hopeful that somebody else had this same issue.    


Any help greatly appreciated.


Below are some notes





Data Source:    SSAS 2016 Tabular Model (on premise for now)

Power BI Model:  Live Connection to Tabular Model



Use Cases:



1:   I am a user and can see all structures in an Operating Group

2 :  Use case 1 and selected structures in another Operating Group

3:   I can only see structures that I have clients in.



When a user logs into Power BI.

We will filter the Structure table based on the user's Power BI or Username

With foreign keys to fact tables on the Structure ID the user will only see values associated to the structures he / she can see.





User Security Table


Structure ID

User Email (Power BI Login)

User Name (Username)


















Structure Table


Structure ID




Group 1



Group 2



Group 3



Group 4



Group 5




Fact Tables:


Filtered on Structure ID to present facts to the user for those structures he/she is able to view.


When Paul Scholes logs in, he will only see Groups 1, 2 and 3's data

When Franz Beckenbauer logs in he will only see Group 4 data

Super User
Super User

Hi there

If you look at the patterns I would suggest that the first table to get filtered is the users security table, which will then have a relationship to the Structure table and will be filtered by the Users Security table?

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

Proud to be a Super User!

Power BI Blog



Thanks for the response.  That's what I have in place.


When a user logs in the user security table gets filtered on his/her email.   The issue is that the current pattern of filtering within SSAS 2016 is that a user can have access to multiple structures.  The Lookup function w/in the SSAS DAX libary expects a single value.  


I'm hoping there is a solution that will work for a 1:Many relationship on user  -> structure.


This works perfect in Import Model, however due to scale we have to move to SSAS based model.



Hi there,

It should work if the lookup is based on just the username. This is because it will return all rows where the username is found

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

Proud to be a Super User!

Power BI Blog

I've made some progress.


This works as expected in SSAS, in that the users get filtered as expected.


I'm using a simple filter where the user security table is filtered by the USERNAME() function.


Still doesn't work in Power BI.    


I'm working with my network group as this might be a Kerberos issue.  as I noticed the NT Canoniacle User Name (has the domian on it) isn't getting passed when I connect to Power BI Service.



Helpful resources

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors