Counting # of instances in a period where criteria used evaluates multiple records and fields for a single instance

[ollieotis]

Hello,

I am trying to calculate the number of times an instance occurs in timesheet data, where an instance is defined as:

Per Day: >= 3 Employees from same Department worked on same Customer Account, and >=3 of same Employees worked >2 hours each. In other words, I am looking to identify where potentially department time has been applied 3x in a day to a customer account.

The table has ~500k records and has multiple other columns, but is basically setup as follows:

Date	Department	Customer	Employee	Hours
1/4/2025	Home	ABC	Doe, Jane	4
1/5/2025	Away	XYZ	Doe, John	2
1/4/2025	Home	ABC	Smith, John	3
1/4/2025	Home	ABC	Jones, Sam	3

In the above data, this would calculate as one instance as Home Department employees Jane Doe, John Smith, and Sam Jones all worked >2 hours on the same customer in a single day.

I got as far as the following measure, but it only provides me with a count of instances where an employee has worked more than 2 hours:

COUNTROWS ( FILTER ( SUMMARIZE (Timesheets , Timesheets[Date] , Timesheets[Department] , Query [Customer] , Timesheets[Name] , "Instance Hours" , sum ( Timesheets[Hours] )) , [Instance Hours]>=2))

I'm stuck at how to incorporate the other logic needed into this measure.

Any advice would be much appreciated.

Thanks in advance!