Applying Row Level Security using LIKE

[dancarr22]

Hello,

We have RLS (row level security) set up on our data model and it is working properly.

But, we have a new request which I need assistance with: how do you set up RLS for a table where we are filtering using LIKE.  i.e. Only display where field DeptID STARTSWITH 'HR%'.  There is no 'begins with' option and if I go into the DAX editor it does not recognize STARTSWITH.  Accessing this from Modeling -> Manage Roles.

STARTSWITH([DeptID], "HR") not recognized/does not work.

Apprecate any assistance you can provide.

Thanks,

Dan

[dancarr22]

Thanks all - @rohit1991  @danextian  and @lbendlin for your help.  Worked as you mentioned - and did see a bit of a performance hit.

[rohit1991 · Accepted solution]

Hi @dancarr22 ,

Thanks for reaching out. You're correct that the STARTSWITH function isn't supported directly in the DAX expression editor within the "Manage Roles" interface for Row-Level Security. However, you can achieve the same functionality using the LEFT function or the SEARCH function in combination with a logical comparison.

For your use case, a simple workaround would be to use an expression like LEFT([DeptID], 2) = "HR", which effectively mimics the STARTSWITH behavior. Alternatively, you can use SEARCH("HR", [DeptID], 1, 0) = 1, which checks if "HR" appears at the beginning of the DeptID field. Either of these approaches should allow you to implement the desired RLS rule based on prefix filtering. Let me know if you need help applying this in your specific model.

[danextian]

You can do LEFT([column], 2) = "HR".

[lbendlin]

You can use FIND or SEARCH for that and validate if the search term is at position 1.

Be prepared for a massive performance impact though.