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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
backflash
Helper II
Helper II

Alternative to USERELATIONSHIP due to Row Level Security?

Hey There,

 

I already read a lot of topics in the blog regarding the issue that Measures with USERELATIONSHIP are not working when there is a Row Level Security on one of the tables that are used in the UseRelationship() Function.

I already read about alternatives like INTERSECT and TREATAS, but somehow I cannot get it build in my scenario in order to replace the measure that is not working for RLS-reasons.

 

I have a simple measure that counts all the ticket in a system that have been created for a customer.

As there is no relationship between tickets (J_issues) and customer (clxT_Customer), I have to go via the deactivated relationship between rickets and Projects and between projects and customers.

 

Ticket-Count = CALCULATE(DISTINCTCOUNT(J_Issue[IssueKey]),
USERELATIONSHIP(J_Issue[IssueProjectKey], J_Project[ProjectKeyName]),
USERELATIONSHIP(clxT_Customer[CustomerKey], J_Project[CustomerKey]))

This is working fine for me - but all colleagues with RLS-roles in place cannot see the results, as :

backflash_1-1664375163409.png

 

 

So I want to replace the USERELATIONSHIP with INTERSECT, but somehow it is not calculating as expected.

It worked with another eample where I only had one USERELATIONSHIP as filter in my CALCULATE function - but with this example where I have two

filters, it is no longer working.

 

Any ideas how I could incorporate the filter in a different way that it works?

 

backflash_0-1664375147299.png

 

 

btw: I dont want to write my measure via goind the fact table 'worklog' because I want to count all the issue, no matter if there is a worklog on it or not.

This is why I want explicitely calculate it via the relationship to projects and customers.

 

Looking forward to your ideas.

2 REPLIES 2
backflash
Helper II
Helper II

Hi @v-tangjie-msft 

sadly this did not solve my problem.
I find your information helpful on how the INTERSECT is working, but my problem is that I cannot change my Measure formula to INTERSECT because I use a double CROSSFILTER in my DAX and I was not able to rebuilt this with INTERSECT.

How would you rebuilt the following statement with INTERSECT? I tried to 'nest' the different tables but it was not working.
Have you any idea on that?

Ticket-Count = CALCULATE(DISTINCTCOUNT(J_Issue[IssueKey]),
USERELATIONSHIP(J_Issue[IssueProjectKey], J_Project[ProjectKeyName]),
USERELATIONSHIP(clxT_Customer[CustomerKey], J_Project[CustomerKey]))


And, did you yourself set your reply as solution? Because I did not and now it was set automatically as solution?
this is strange to me... is this an automatic feature from Power BI forum?

 

Regards

Vanessa

v-tangjie-msft
Community Support
Community Support

Hi @backflash ,

 

In the Document  mentioned: USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included. 

 

The INTERSECT function returns a table containing only the rows that are present in both table expressions as arguments. Both tables must have the same number of columns and data types, and the columns are combined by position in their respective tables. The first parameter defines the name of the column in the result, and subsequent parameters can modify the number of rows returned without affecting the column names.

 

The following points need to be noted when using the INTERSECT function.
1. The parameters of INTERSECT are not interchangeable. Usually, the result of INTERSECT(T1, T2) has a different meaning than the result of INTERSECT(T2, T1).
2. Keep duplicate rows. If a row appears in both parameters of INTERSECT, then it and all its identical rows in the Left Table are returned.
3. The column names of the returned table match the column names in the Left Table. Having data along in the Left Table is independent of the columns along in the second table. For example, if the first column of the Left Table has an inheritance to the model's column C1, INTERSECT removes those rows that exist only on the Right Table and leaves the inheritance of the base column C1 unchanged.
4. Columns are compared according to their position, and no forced type conversion is performed for data comparison.
5. The returned table does not include the columns of the table associated with the LeftTable, i.e., extended table theory is not supported.

 

You can use the technique defined in this blog post Solving RLS Gotchas – Prologika

How to Maximize The Use of INTERSECT Function - Advanced DAX (enterprisedna.co)

 

I hope this link will help to resolve your problem,

Solved: USERELATIONSHIP() and RLS - Microsoft Power BI Community

Solved: UseRelationship() and RLS constrained - Microsoft Power BI Community

 

 

Best Regards,

Neeko Tang

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors