RLS with USERELATIONSHIPS all over the place

[andrea_chiappo]

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?)

[amitchandak]

@andrea_chiappo ,refer id these can help

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

[andrea_chiappo]

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]

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.

[andrea_chiappo]

Thank you for your reply @v-yingjl  

I realised that I did not explain myself properly. I will modify the question from the start.

[v-yingjl]

Hi @andrea_chiappo ,

Could you please consider sharing a dummy .pbix file or your rls role expression which uses userelationship() and crossfilter() for further discussion? Maybe it can be written as another way to avoid this error.

Best Regards,
Yingjie Li

[andrea_chiappo]

the rls expression is simply: [email] = "employee@address.com"

If you kindly check the update version of the post you will find a detailed explanation of the situation.  

No USERELATIONSHIP or CROSSFILTER appears in the rls expression. The issue is in the model and appears to be a limitation of DAX