CONTAINSROWS error in Row Level Security DAX expression

[cathoms]

Hello. We have a few tables we use for setting up RLS, as follows:

ADUserView_RLS

UserID     PowerBILogonID

Service_Area_10_Lookup_RLS

SERV_AREA_ID     SERV_AREA_ABBR

UserBusinessSegmentMappingFact_RLS

BusinessSegmentKey     EmployeeEpicId

I created a role called Dynamic Role and I get the following error: "The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression." I can't figure out what the issue is and would appreciate any help. Here is the DAX expression used to filter the DepartmentDim table:

VAR lookupUserID = LOOKUPVALUE(ADUserView_RLS[UserID],ADUserView_RLS[PowerBILogonID], userprincipalname())
    
VAR lookupDepartment = SUMMARIZE(FILTER(UserBusinessSegmentMappingFact_RLS, UserBusinessSegmentMappingFact_RLS[EmployeeEpicId] = lookupUserID), UserBusinessSegmentMappingFact_RLS[BusinessSegmentKey])
    
VAR lookupServiceArea = SUMMARIZE(FILTER(DepartmentDim, DepartmentDim[DepartmentKey] in lookupDepartment), DepartmentDim[ServiceAreaEpicId])
    
RETURN
IF(
    COUNTROWS(Filter(lookupServiceArea, [ServiceAreaEpicId] IN {"10"})) > 0,
    [ServiceAreaEpicId] in Service_Area_10_Lookup_RLS,
    [ServiceAreaEpicId] in lookupServiceArea
    )

[cathoms · Accepted solution]

I reviewed the CONTAINSROWS documentation and realized that in my return statement

[ServiceAreaEpicId] in Service_Area_10_Lookup_RLS,

is looking to match one field in a table with two columns. Rather than try to get the CONTAINSROWS syntax right and match on multiple columns, I ended up creating a new variable (sa10rls) to select just the one appropriate column for that match. Here is the whole thing, which now seems to work:

VAR lookupUserID = LOOKUPVALUE(ADUserView_RLS[UserID],ADUserView_RLS[PowerBILogonID], userprincipalname())
    
VAR lookupDepartment = SUMMARIZE(FILTER(UserBusinessSegmentMappingFact_RLS, UserBusinessSegmentMappingFact_RLS[EmployeeEpicId] = lookupUserID), UserBusinessSegmentMappingFact_RLS[BusinessSegmentKey])
    
VAR lookupServiceArea = SUMMARIZE(FILTER(DepartmentDim, DepartmentDim[DepartmentKey] in lookupDepartment), DepartmentDim[ServiceAreaEpicId])

VAR sa10rls = SELECTCOLUMNS(Service_Area_10_Lookup_RLS, Service_Area_10_Lookup_RLS[SERV_AREA_ID])
    
RETURN
IF(
    COUNTROWS(Filter(lookupServiceArea, [ServiceAreaEpicId] IN {"10"})) > 0,
    [ServiceAreaEpicId] in sa10rls,
    [ServiceAreaEpicId] in lookupServiceArea
    )

[Anonymous]

Hi @cathoms ,

Notice that you are using the IN operator, so you may need to use the containrows function, which you can refer to for details:The IN operator in DAX - SQLBI

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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