Forum Discussion

andrea_chiappo's avatar
andrea_chiappo
Icon for Helper III rankHelper III
6 years ago

RLS with USERELATIONSHIPS all over the place

Hi Power BI experts,

 

NEW VERSION

 

The data source in my Power BI file are Excel workbooks, one for every client of the company I'm working for.  

I built several report pages, where all visuals are controlled by three main slicers, which repeat across all pages:  

- client name (taken from a DimCustomers table)  

- employee roles (also taken from the DimCustomers)  

- date (take from CalendarDimUser table)  

 

When no role is defined, the reports look like this

 

The visuals (in this page as well as others) are based on Measures and Calculated columns, using all sorts of DAX functions  

(USERELATIONSHIP, CALCULATE, ALLEXCEPT, DISTINCTCOUNT, COUNTROWS etc.)  

 

The rationale in proceeding this way was to create a unique Power BI model, suitable for all clients, and then defining roles for the Power BI Service level. This way, I can control what each client sees (eg. the responsible from Company Inc. who will received the link to the dashboard, will only see data from Company Inc. and so on for all clients).  

 

To achieve this, I created a DimCustomerPermissions table as follows

(the three email addresses are from internal employees and refer to mock client companies, meant only to test everything)  

 

I then created the role for one of these three and assigned the corresponding email address, with the following RLS:

this format is only meant for testing the approach. 

In the real case, I would replace the explicit email with USERPRINCIPALNAME()  

 

However, this is what I get when I try viewing the report as the role just created 

 

The whole thing falls apart.  

 

Disturbingly, from the error message this appears to be a limitation of Power BI, rather than a mistake (but I might very well be wrong) 

 

Given all this, do you think that what I am doing is feasible or the approach is intrinsically flawed (unique model for different data sources?)

Need Help

6 Replies

    • andrea_chiappo's avatar
      andrea_chiappo
      Icon for Helper III rankHelper III
      6 years ago

      Thank you amitchandak 

      However the Dynamic RLS does not solve my problem as the issue appears to be beyond that  

       

      All tutorials and pages I´ve found present cases of RLS for extremely simplified models  

      (simply showing part of the data and/or measures/columns involving simple DAX functions)  

      The nature of my problem appears to be a fundamental limitation, where more high-level DAX is not allowed  

      (involving CALCULATE, ALLEXCEPT, USERELATIONSHIP and so on....)  

       

      Is there a solution to this? Thank you to all you can provide some further support/insight

      • v-yingjl's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi andrea_chiappo ,

        When using dax expression to define RLS in power bi desktop, most dax functions are available except you cannot use it to create a virtual table becasuse RLS filters all fact tables.

        As for you cannot use functions like calculate, I don't think so. Refer to my following expression in the picture using the calculate() function is RLS:

        This role defines each person can show their own datas under a manager-hierarchy level, sample file attached that you can refer:

        RLS expression.pbix 

         

        Best Regards,
        Yingjie Li

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.