Forum Discussion

FrancescoSCP's avatar
FrancescoSCP
Icon for Helper IV rankHelper IV
7 years ago

Multiple RLS roles

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 ...
Need Help
jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
7 years ago

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's avatar
    FrancescoSCP
    Icon for Helper IV rankHelper IV
    7 years ago

    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