Count of employees without Incidents

DAX DAX Calculations
1

Hello lovely people :slight_smile:

I would like to ask for suggestions/ideas.

I need to count employees’ ID who didn’t cause any Incidents and I have a situation like in the picture below ( tables are connected based on Employee ID columns).

image
Employees for Incidents 02082023.pbix (18.9 KB)

I can’t really use this solution below because, I have to count later Employees with Incidents, Employees with Claims, Employees without claims, Employees without… etc, etc. and this gives me so many tables and make it very slow.

image
image967×558 63.1 KB

Any help/advice is highly appreciated.

Regards,

Iwona

1 Like
2

Hi,

I added a calculated column into your Employee tbl

No of Incidents =
CALCULATE ( COUNTROWS ( Incidents ) )

image

Employee Incidents =
SUM ( Employees[No of Incidents] ) + 0

image
image706×365 4.1 KB

Employees for Incidents 02082023 Iwona.pbix (20.4 KB)

3

Hi @Iwona did my response provide the solution you were looking for?

4

@Iwona, In addition to @DavieJoe’s suggestion you could also use the INTERSECT() and EXCEPT() functions:

Employees with 0 incidents 
Employees with 0 incidents = 
    VAR __employees = VALUES ( Employees[Employee ID ] ) 
    VAR __incidents = VALUES ( Incidents[Employee ID ] ) 
RETURN

COUNTROWS ( 
    EXCEPT( 
        __employees 
        , __incidents 
    ) 
)
Employees with 1+ incidents 
Employees with 1+ incidents = 
    VAR __employees = VALUES ( Employees[Employee ID ] ) 
    VAR __incidents = VALUES ( Incidents[Employee ID ] ) 
RETURN

COUNTROWS ( 
    INTERSECT ( 
        __employees 
        , __incidents 
    ) 
)

image

2 Likes
5

Nice @HufferD, I’ll be bookmarking that DAX for future reference.

1 Like