# Count rows in one table with 2 criteria

Hi i have an attendance table that i want to count each employees sickness days (S) and unauthorized absence days (A) from

I have grouped all the id numbers and the with the attendance type and then created the 2 measures

``````Days Absent =
CALCULATE (
COUNTROWS ( '2019 Attendance Data' ),
'2019 Attendance Data'[Absence Type] = "S"
)

Separate Absences =
CALCULATE (
DISTINCTCOUNT ( '2019 Attendance Data'[Block Index] ),
'2019 Attendance Data'[Absence Type] = "S"
)
``````

This works fine when i just want the results of sickness only (S) however i want to also return the unauthorized absence days (A) how can i modify the above to find both criteria? its not the same as excel and just use &.

Thanks

Dan

Hi @Krays23,

Try something like:

``````Days S+A =
COUNTROWS(
CALCULATETABLE(
'2019 Attendance Data',
FILTER(
'2019 Attendance Data',
‘2019 Attendance Data’[Absence Type] = “S” &&
‘2019 Attendance Data’[Absence Type] = “A”
)

)
)

Absences count S+A =
CALCULATE(
DISTINCTCOUNT( ‘2019 Attendance Data’[Block Index] ),
FILTER(
‘2019 Attendance Data’,
‘2019 Attendance Data’[Absence Type] = “S” &&
‘2019 Attendance Data’[Absence Type] = “A”
)
)
``````

HI Mel,

I couldn’t get your method to return any results at all can you have a look at the PBIX below?

CCA Attendance.pbix (1.3 MB)

Hi Kra,

Thanks for supplying a PBIX, this made it easy to fix. Changed the measures as follows:

``````Days S+A =
COUNTROWS(
CALCULATETABLE( '2019 Attendance Data',
'2019 Attendance Data'[Absence Type] IN { "A", "S" }
)
)

Absences count S+A =
CALCULATE(
DISTINCTCOUNT( '2019 Attendance Data'[Block Index] ),
CALCULATETABLE( '2019 Attendance Data',
'2019 Attendance Data'[Absence Type] IN { "A", "S" }
)
)
``````

I hope this is helpful. Here’s the updated PBIX
eDNA Forum - CCA Attendance.pbix (1.3 MB)

1 Like

Awesome thanks Mel

Dan