10-18-2018 12:15 PM
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
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
User Email (Power BI Login)
User Name (Username)
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
10-18-2018 09:01 PM
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?
"Proud to be a Datanaut!"
10-19-2018 07:39 AM
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.
10-20-2018 02:15 PM
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
"Proud to be a Datanaut!"
10-22-2018 03:35 PM
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.