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

[jasonbakersd]

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

[GilbertQ]

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?

[jasonbakersd]

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.

[GilbertQ]

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

[jasonbakersd]

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.