Power Apps Canvas - Many to Many to Many relationship Filter

Question

I have the following Many to Many structure within CDS for three entities: User, Department and SuperGroup

• User can be in many Departments (Department can have many Users)
• Department can be in many SuperGroups (SuperGroup can have many Departments)

Within my Canvas App, I have built a collection of SuperGroups which I show within a Gallery. This collection needs to be filtered for the Current User so that only SuperGroups that are within Departments assigned to the current users are shown.

I have tried putting the Departments a User is assigned to within its own Collection first(colActiveUserDepartments) and then using the following statement:

ForAll(
 colActiveUserDepartments,
 Collect(SuperGroupsCollection,
  Filter([@SuperGroups],
   cus_departmentid in ThisRecord.Departments.Department
  )
 )
)

I think this is working though it could show duplicates which I think I could remove by building another Collection which takes only distinct records from SuperGroupsCollection.

Is there however an overall better way of achieving this requirement?

Answer 1

Did you try the default N:N way to show items using the way explained here: https://powerapps.microsoft.com/en-us/blog/option-sets-and-many-to-many-relationships-for-canvas-apps/

Also see: https://powerusers.microsoft.com/t5/Building-Power-Apps/Filtering-a-gallery-CDS-and-Many-to-Many-relationship/td-p/684092