Forum Discussion

jasonbakersd's avatar
jasonbakersd
Icon for Helper I rankHelper I
7 years ago

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. ...
Need Help
GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
7 years ago
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's avatar
jasonbakersd
Icon for Helper I rankHelper I
7 years ago

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's avatar
    GilbertQ
    Icon for Super User rankSuper User
    7 years ago
    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's avatar
      jasonbakersd
      Icon for Helper I rankHelper I
      7 years ago

      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.