Forum Discussion
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
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
)
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 )
2 Replies
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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 )
