Getting Error While Viewing report as a Another User (RLS).

[sarjensystems1]

Hello All

I have created a power Bi Report, having relationships between Sales Table & Division Table with 2 Different Way and Offcourse we can active only single relation at a time, and it's working fine on desktop when I want to filter Material Wise Sales(Used Userrelationship in dax) Or CustomerWiseSales (Direct Relationship) . Attaching screenshots for your refrence. In the second Screenshot you can see it's working fine on Power BI Desktop, But in Second Screenshot When I am viewing as another User it's Giving Error. WHY?? IT'S GIVING ERROR WHEN I AM TRYING TO VIEW AS AN ANOTHER USER BUT NOT AS AN ADMIN... ANY SUGGESTIONS PLEASE??

[tex628]

Hi @sarjensystems1 ,

Can you take a picture of the relationship-path between SDirectSales and SDivision, as well as an image of the RLS that you're applying? 

I'm rather sure that this has to do with the fact that relationshipbehaviour is slightly differernt in regards to doublesided crossfilters when you're applying RLS. 

Br, 
J

[sarjensystems1]

hi @tex628  Below is the screenshot of relationship between SDivision & SDirectSales and The "User role" is the once having mail id's for RLS. Hence, having BI-directional relation with Division. If i turn it into Single then The RLS Won't work. i.e, End User will get every Division.

[tex628]

Can you open up and take a picture of the relationship between user roles and division? You can crop out all the data

[sarjensystems1]

Hi @tex628 

[tex628]

From what i can gather there are issues related to using USERELATIONSHIP() while having an active RLS. If i understand it correctly it is because the RLS security takes precident and forces the system to try and apply both relationship-paths at the same time, in order to apply the RLS.

From a little research it looks like the workaround is removing the relationship and using TREATAS() instead of USERELATIONSHIP().  

powerbi - DAX measure not working when RLS is turned on - Join paths are expected to form a tree - S...

If you use the filtered values in SMaterial in the TREATAS() statement while also temporarily removing the CustomerSupplier relationship with a CROSSFILTER() statement i believe you should be able to get the correct result. 

Br, 
J

[sarjensystems1]

Hii @tex628 

Your approach is working fine while applying RLS but it's not working Without RLS. In Visual it's showing same value of sales for every material or Customer.

[tex628]

This is getting quite complicated 🙂 But i believe this is due to the fact that we are removing the relationship and trying to combine the measure which is calculated on the Sales Table with a dimension column from the material table which no longer has any relationship to the sales table. 

I believe that you can work around this by using SUMMARIZE() & SUMX() to calculate the values onto the SMaterial table meaning that the relationship won't be necessary. 

It should looks something along the lines of this:

Measure = 
VAR MaterialNo = SELECTEDVALUE(SMaterial[Material No])
Return
SUMX(
SUMMARIZE( 
SMaterial, 
SMaterial[Material No], 
"Value" , CALCULATE( [Sales Amount] , SDirectSales[Material No] = MaterialNo , CROSSFILTER( SDirectSales[Customer No] , SDirectSales[Customer No] , NONE) ,
[Value]
)

Let me know how it goes! 

/ J