cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jasonbakersd Regular Visitor
Regular Visitor

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

 

 

Architecture:

 

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)

1

Paul.Scholes@mufc.com

PScholes

2

Paul.Scholes@mufc.com

PScholes

3

Paul.Scholes@mufc.com

PScholes

4

Gerd.Muller@bmfc.com

GMuller

4

Franz.Beckenbauer@bmfc.com

FBeckbauer

3

Dino.Zoff@Juve.com

DZoff

5

Dino.Zoff@Juve.com

DZoff

 

 

 

Structure Table

 

Structure ID

Group

SubGroup

1

Group 1

West

2

Group 2

Central

3

Group 3

South

4

Group 4

East

5

Group 5

North

 

 

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

4 REPLIES 4
Super User
Super User

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

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 Datanaut!"
jasonbakersd Regular Visitor
Regular Visitor

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

Gilbert,

 

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.

 

 

Super User
Super User

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

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 Datanaut!"
jasonbakersd Regular Visitor
Regular Visitor

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

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.