CONTAINSSTRING in Security Roles not allowed?

[ptmuldoon]

I am working/learning to convert a pbix file and data model into the PB service, and having an issue with Permissions Role I have in my pbix file failing on the service.

I have this in Role in my pbix file which uses ContainsString.  

CONTAINSSTRING(
    MAXX(
        FILTER(
            Users,
            Users[Email]=USERPRINCIPALNAME()
        ),
        Users[Regions]
    ),
    [Region]
)

But the code above gives an error message when I copy it into the model/role on the service of:

Function 'CONTAINSSTRING' is not allowed as part of the row level security expression on DirectQuery models. 

Can anyone help how I need to modify that code to work?  I've been reading about trying with LIKE and INSTR, but have not gotten this working yet.

Thanks

[Anonymous · Accepted solution]

Hi @ptmuldoon ,

As far as I know, the CONTAINSSTRING function is really not supported in row-level security (RLS) role queries. This may limit your flexibility in defining roles. I recommend that you complete the task of obtaining the column maximum value and determining whether the value contains the maximum value in the report. For example, create measure.

The security role:

=USERPRINCIPALNAME()

Create measure in report.

Measure = VAR maxregion = CALCULATE(MAX('Users'[Regions]),ALL('Users'))
RETURN IF(FIND(maxregion, MAX('Users'[Regions]), 1, 0) > 0, "Found", "Not Found")

Filter measure equals "Found" and lock the filter.

Best regards,

Mengmeng Li

[ptmuldoon]

Thanks,

I'll try and give that a shot.  But I think that means I would need to set that Measure and Filter on every report page correct?    Or do you think that will also work within the RLS and adding to the Security Role?

[Anonymous]

Hi @ptmuldoon ,

I think so. Because security role queries in direct quer mode are not supported for many DAX functions, this is due to query performance considerations at the beginning of the design. It is reassuring that RLS filtering always takes precedence over DAX queries in reports. 

Best regards,

Mengmeng Li

[ptmuldoon]

I've been experimenting and while I still haven't figure this out yet, I'm not sure if this is an issue with CONTAINSSTRING or not.

In the below code, If I hardcode a value, it appears to work.  But when I try to use the returned result of variable, I still get the same error?

Var UserRegions = 
        var filteredTable = FILTER(Users, Users[Email]=USERPRINCIPALNAME())
        return SELECTCOLUMNS(filteredTable,"Region", Users[Regions])

RETURN
//CONTAINSSTRING([Region], "Test")
CONTAINSSTRING([Region], UserRegions)