Forum Discussion
CONTAINSROWS error in Row Level Security DAX expression
- 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 )
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
)