cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jessset
Helper I
Helper I

Cross Filter None Alternative for Row-Level Security

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!

 

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors