Count Rows with Filter function on multiple tables

(0) Share

Report

Posted on by 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)))

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

v-qiaqi@microsoft.com on at

Like (0)

Report
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.

Was this reply helpful? Yes No
Nealsum 14 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

v-qiaqi@microsoft.com on at

Like (0)

Report
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

Was this reply helpful? Yes No
Nealsum 14 on at

Like (0)

Report

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

Was this reply helpful? Yes No