Count rows in one table with 2 criteria

DAX DAX Calculations
1

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

Capture
Capture.JPG769×673 74.2 KB

Thanks

Dan

2

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”
    )
)

I hope this is helpful.

4

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)

5

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
6

Awesome thanks Mel

Dan