Cross Filter None Alternative for Row-Level Security

[jessset]

Hi there,

I currently have a measure as below which works fine when RLS is not activated (I need to deactivate the relationship for this particular measure.

CALCULATE(COUNT(Sales[SalesID]),CROSSFILTER(XLS_Branch[Location],Property[Location],None))

However, when I turn on RLS, it says that CrossFilter() functions cannot be used....

Is there an alternative I can use to deactivate my relationship (perhaps using TREATAS function)?

Thanks!

[Anonymous]

Hi @jessset ,

About CROSSFILTER, here're some remarks:

• In the case of a 1:1 relationship, there is no difference between the one and both direction.

• CROSSFILTER can only be used in functions that take a filter as an argument, for example: CALCULATE, CALCULATETABLE, CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR, OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, TOTALMTD, TOTALQTD and TOTALYTD functions.

• CROSSFILTER uses existing relationships in the model, identifying relationships by their ending point columns.

• In CROSSFILTER, the cross-filtering setting of a relationship is not important; that is, whether the relationship is set to filter one, or both directions in the model does not affect the usage of the function. CROSSFILTER will override any existing cross-filtering setting.

• An error is returned if any of the columns named as an argument is not part of a relationship or the arguments belong to different relationships.

• If CALCULATE expressions are nested, and more than one CALCULATE expression contains a CROSSFILTER function, then the innermost CROSSFILTER is the one that prevails in case of a conflict or ambiguity.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

You can try USERELATIONSHIP instead of CROSSFILTER.

Best Regards,

Stephen Tao

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

[edhans]

CROSSFILTER works fine with RLS in import mode. You are likely using DirectQuery. TREATAS() will not change the model the way CROSSFILTER will. This is one of many reasons not to use DirectQuery - so many limitations in DAX.

[jessset]

Thanks @edhans! 

I've just checked my data model again and can confirm I'm not using any Direct Queries, I've imported all my queries.

This is the error I get when I turn on a role:
"The UseRelationship() and CrossFilter() functions may not be used when querying 'Sales' because it is constrained by row-level security defined on 'XLS_Branch' or related tables."

I've managed to find a workaround by using ALL() function. Thanks!

[QuyenHoang]

Hi Jessset,
Can you elaborate on the operation using All() instead of CROSSFILTER()?