Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
andrea_chiappo
Helper III
Helper III

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

Annotation 2020-05-19 141831.jpg

 

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

Annotation 2020-05-19 143900.jpg

(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:

Annotation 2020-05-19 145842.jpg

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) 

Annotation 2020-05-19 145447.jpg

 

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

6 REPLIES 6

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

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:

RLS.png

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.

Thank you for your reply @v-yingjl  

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

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

 

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.