Scenario: You’ve created a beautiful, wide-open Tabular model. You use USERELATIONSHIP() to switch relationships on and off. Everything works and everyone is pleased. Then RLS sneaks in, such as when external users need access, and you must secure on some dimension table. You create a role, specify a row filter, test the role, and get greeted with:
The UseRelationship() and CrossFilter() functions may not be used when querying ‘<dimension table>’ because it is constrained by row-level security defined on ‘<dimension table>’ or related tables.
Analysis: There is a long-standing Tabular limitation that prevents USERELATIONSHIP for an added level of security which may be triggered even if USERELATIONSHIP doesn’t enable a relationship on the security propagation path. This is done to prevent information disclosure in case there is some other active relationship (since UseRelationship would disable security propagation across the other relationship). Unfortunately, the current design is “no inactive relationship, no problem”. A better option would have been to introduce a metadata table-based (or relationship-based) attribute to remove this rule.
Workaround: Since currently there is no magic switch you need to find a workaround depending on your specific case. For example, in one case where only external users were affected, I added a new set of measures. I didn’t change the original measures for two reasons: a) avoid re-testing the entire model and b) dynamic relationship always underperform materialized relationships. The new set could use INTERSECT (or TREATAS if you on SQL Server 2016+) to replace USERELATIONSHIP. For example, instead of:
USERELATIONSHIP(Policy[Branch Number], Division[Branch Number])
You could use:
INTERSECT(VALUES(Division[Branch Number]), VALUES(Policy[Branch Number]))
Note that you might not get exactly the same behavior because materialized and dynamic relationship differ in how the missing members are handled (see my blog “Propagating DAX BLANK() Over Relationships” to understand this better).
INTERSECT worked for me…thanks!
Not Sure how the intersect its can be used inside a calculate function similar to how userelationship is used! can you please provide full example with how the final measure will look like? Thanks
So, instead of CALCULATE(…, USERELATIONSHIP(…)) you’d use CALCULATE(…, INTERSECT()) as the example in the blog. Because INTERSECT returns the table, the measure will filter the result over that table, producing the same result as USERELATIONSHIP.
Thank you so much for the reply . It worked like a magic! The intersect documentation doesnt mention anything about it being used as a calculate function and that is why I got confused. You are awesome :). Can you please explain why I get different results when I change the location of each table VALUES. I thought VALUES will return a unique set therefore the result will be the same regardless where each table location is.
Such a valuable DAX function.
Thanks a ton Teo, it worked beautifully. 🙂