Count within many to many relationship

edited August 2023 in Modeling


I am trying to solve below problem and was seeking help for an optimal way.

I have a module which has a many to many relationship between Employees and Offices. I want a count of all unique employees within 1 office. For example in the below scenario, I have 3 offices with employees working from that office (1 employee can work from multiple offices). There is also a condition applied for each employee, only if that is satisfied the employee should be eligible for the count.

I am expecting to get the count as below

Thank you.


Best Answers

  • rob_marshall


    Create a line item that concatenates both emploee and office Id. Then create another that is a counter (1) if the employee/office Id is unique…

    if isfirstoccurence(concat item, list of the module) then 1 else 0…then do a sum of that by the office id list.

  • yatin.ajbani

    Thank you Rob