RLS - Assign 2 security roles to a user

[nanma94]

My simplified data model is like below - "Prod level 1" and "GEO" are distinct valued tables from my two dimension tables - Prod mapping and GeoCountries - so I can create many to many relationship between my security tables and the 2 dimension tables. 

Now I want a user to be limited to APAC region, and Prod A only. I added this user in both security tables - secure by prod, and secure by GEO, and assigned both sucrity roles to him. When I test it in PBI desktop (image 2) , the security doesnt do anything. If I only select 1 role to test, I can see this user is limited to see APAC only but for all products,, or Prod A only but for all GEOs. 

Is there a way I can make both security roles to work on a user?

Thanks a lot, 

NM

[Anonymous]

RLS is an additive security system.  Whatever you secure by, they will have an all of those allows added together.

The next part is that your filtering has to work.  Without RLS, can you set up 2 filters on a test report page and get the filtering to work there as well?

[nanma94]

Thank you Ross. If I understand your reply correctly, I created a visual table to show the data flow from secure by GEO -> GEO -> GeoCountry. 

And you are absolutely correct it throws error saying cant determine the relationship between 2 or more fields. 2 tables connect - either Secure by GEO/GEO, or GEO/GeoCountry, but all 3 tables dont connect and throw error. What could be the reason? My GEO table is just a summarize function  all GeoCountry[GEO] column. and the relationship is bidirectional. 

Regards, 

NM

[Anonymous]

Take a snip of the table relationship from inside Power BI. I'm expecting you are trying to link data through a Many to one to Many relationship in a way that doesn't make sense.  Bi-directional can give you a false sense of security.

[nanma94]

@Anonymous Ross I dont think I Understand. Can you give me more hint:)

Thanks much. 

NM

[Anonymous]

On the left hand side of Power BI Desktop is a button that changes the view into table "Relationships".  I'm hoping see what you have there to help answer your question.

  

You can use a Windows Program called "Snipping Tool" to take a snapshot of part of your screen.

[nanma94]

@Anonymous here you go, a very busy model - the red and blue lines are to secure data by prod, and seccure by GEO

Thanks!
NM

[Anonymous]

Wow, yeah your data model is certainly the problem.  You need to start again and redesign this.  You are trying to relate data that cannot be related based on how it is presented.  I would question if you have created unncessary tables that should be combined to create a streamlined model.

I also should point out that it looks like you might be putting in too many 2 way relationships.  They do some great things, but they allow you to make errors that come back to bite you once you expand your model.