Multiple RLS roles

[FrancescoSCP]

Hi all,

i've created a simple data model on Power BI desktop:

• Fact_Table Sales
• Dim_Products
• Dim_Agents

I have two hidden table with this structure:

• RLS_Products: Id_Product | Username
• RLS_Agents : Id_Agent | Username

So i've created two role with a filter on the respective table: [Username] = USERNAME().

If i put an User in Role_Products everything works good. Also if i put an User in Role_Agents.

But if i put the same User on both roles (let's say he can see only data of Agent 1 and 2, and o Bikes Product) the report on the online service gives me an error: "The query encountered security filters on a table relationship that cannot be reconciled for the current user. Please contact your administrator or developer to review role memberships and security definitions."

What is the best way to set up multiples RLS based on different dimension?

And how work they together? I presume with the intersection of two roles (AND)...and if i would like to use an OR condition (so a User in both roles can see Agent 1 and 2 OR Product Bikes)? Is a way to do this?

I've seen also that if an User is a Contributor of an workspace the RLS is not applied. What am i doing wrong?

Sorry guys for my english and thanks in advance for your replies.

Regards,

Francesco

[Anonymous]

Hi @FrancescoSCP ,

Is it possible to share some sample sanitizedd data and pbix to arrive at a solution.

You can place to OneDrive or GooldDrive and  share the link here.

Cheers

CheenuSing

[jdbuchanan71]

RLS with multiple criteria is a fun one.  What has worked for me is to create a couple measures that get applied to a single role to do the filtering.

CanSeeThisProduct = 
    CALCULATE ( 
        COUNTROWS(RLS_Products)
        ,'RLS_Products'[UserEmail] = USERNAME()
    )

CanSeeThisAgent
    CALCULATE ( 
        COUNTROWS(RLS_Agent)
        ,'RLS_Agent'[UserEmail] = USERNAME()
    )

Then the role filtering is done with this DAX

Filter on Products is.

[CanSeeThisProduct]  > 0

Filter on Agents is

[CanSeeThisAgent] > 0

For you question on contributors being able to see everything, they can because they have access to modify the datasets.  If you want RLS to apply you would just share the report with the users without making them members of the workspace.

[FrancescoSCP]

Hi,

thanks for your reply!

I also tried with one Role with two conditions and it works, but this is not manageable. We have many (6-7-8) RLS based on different dimension and with this logic we have to create a Role for every combination of RLS.

For contributors: i want to publish my pbix file, which contain my data model, and let users to create personal report based on this dataset with security etc. If i set viewer permission, users are not able to create report. The next level of permission is contributor, but they are able also to delete dataset and RLS is not applied. Is there a trick to achieve my goal?

The last question is the OR condition. Let's do an example:

User A is a manager of Bikes product, but also a manager of Germany. He have to see all sales about Bikes in the world but also Germany with other products. So Bikes OR Germany.

We are thinking to migrate some customers from Oracle Business Intelligence EE to Power BI but we have to be sure that some security levels are also available in Power BI.

Thanks in advance.

Francesco