Forum Discussion
cathoms
2 years agoResponsive Resident
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...
- 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 )
Anonymous
2 years agoNot applicable
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.