Count Rows with Filter function on multiple tables

[Nealsum]

Hi all,

In my power apps, i use dataverse as datasources, my table structures like this:

1. table staff (contact staff information i.e. name...) 

2. table staff payroll (monthly payroll record for each staff)

3. table roster ( daily record of each staff payroll with the duty status)

4. table duty code (each code status is on duty or off duty) 

I would like to get the total of rows count for each staff on that staff payroll month onduty days (duty code is onduty= yes)

I managed to show in the gallery for the total numbers of days in that table roster (31 is total days for Oct) using this formula : CountRows(Filter('Staff Rosters',PayrollID=ThisItem.PayrollID))

However,  I am not able to get total of onduty days using this formula :CountRows(Filter('Staff Rosters',PayrollID=ThisItem.PayrollID, DutyCode=Filter('Staff Roster Types',OnDuty=true)))

[v-qiaqi-msft]

Hi @Nealsum,

Modify as below:

CountRows(Filter('Staff Rosters',PayrollID=ThisItem.PayrollID, DutyCode=LookUp('Staff Roster Types',OnDuty=true).DutyCode))

Note that you should lookup a duty code based on the OnDuty=true in the staff roster types table.

[Nealsum]

Thanks for reply, however it doesn't work work and result in delegation warning.

I would like to count the total of records where the duty code is onduty=yes

here's the staff roster types table

[v-qiaqi-msft · Accepted solution]

Hi @Nealsum,

CountRows() function does cause the delegation warning, which is by design if you have more than 2000 records.

Further, to filter the CodeName with onduty=yes, please modify your formula as below:

Filter('Staff Roster Types',OnDuty='OnDuty ('Staff Roster Types')'.Yes)

Note that you should refer to Field(TableName).Yes

[Nealsum]

I found that my lookup table setting is setup incorrectly, now i can easily get the onduty count. thanks for the update anyway