Forum Discussion

cathoms's avatar
cathoms
Responsive Resident
2 years ago
Solved

CONTAINSROWS error in Row Level Security DAX expression

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   UserBusinessSegmentMa...
  • cathoms's avatar
    2 years ago

    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
        )