Count of employees with several conditions

[Iwona]

Hi all,

I am trying to prepare a report which shows employees with 3 or more incidents -
( (Incidents included are: Collision, Collision – Major, Collision – Minor, Passenger Injury, Passenger Incident (slip, trip, fall))
driver with 3 more incidents for specific cat and subcat.pbix (245.7 KB)

This measure doesn’t give me any outcome. Other attempts don’t even capture correct data.
Drivers with 3+ Incidents =
CALCULATE(
DISTINCTCOUNT(‘FACT_INCIDENT’[EMPLOYEE_ID]),
FILTER(
ALL(‘FACT_INCIDENT’),
‘FACT_INCIDENT’[OP_DATE] >= TODAY() - 365 &&
(
‘FACT_INCIDENT’[CATEGORY] = “Collision” ||
‘FACT_INCIDENT’[CATEGORY] = “Collision - Major” ||
‘FACT_INCIDENT’[CATEGORY] = “Collision - Minor” ||
‘FACT_INCIDENT’[CATEGORY] = " Passenger Injury"||
(
‘FACT_INCIDENT’[CATEGORY] = “Passenger Incident” &&
‘FACT_INCIDENT’[SUB_CATEGORY] = “Slip/Trip/Fall”
)
) &&
CALCULATE(
COUNT(‘FACT_INCIDENT’[INCIDENT_NO]),
‘FACT_INCIDENT’[OP_DATE] >= TODAY() - 365
) >= 3
)
)

I need to achieve two tables:
first table with Location, number of employees ( with 3+ inc) and number of exact incidents for everyone
the second table should store the location Employee ID , Incident ID, Category, and Subcategory .

Would you please help me to find out a solution for my struggle.
Grateful as always .

Iwona

[ankit]

Hi @Iwona - Please find solution in attached file. Currently only Employee fulfilling the criteria is “6357639”. Can use “Num Employees with Incidents > 3_Id” measure to get the Employee ID’s fulfilling the condition.

driver with 3 more incidents for specific cat and subcat_Ankit.pbix (248.0 KB)

Thanks
Ankit J

[Iwona]

Hi @ankit

Grateful for your work on my struggles.

I am trying to validate your solution and load data from the smallest location.
Example of 3 or more - forum 03072023.xlsx (37.3 KB)
PBI table is empty even though there are some people meeting criteria:
driver with 3 more incidents for specific cat and subcat.pbix (245.7 KB)

Also, I am trying to find a way, how I can display employees meeting these requirements.
The second table would give me the expected Employee ID, all Incidents No, their categories, and subcategories.

Right now, those columns don’t correspond withc each other .

Once again, all your efforts and expertise are highly valued and appreciated by me.

Reagrds,

Iwona

[ankit]

Hi @Iwona - Why didn’t you shared actual data earlier as I have to do double the effort now. Anyways
Issue was with the Category names as for example in earlier data it was “Collision - Minor” but now it is “Collision – Minor” (difference in -). I have modified it in new measure “Num_incidents_ankit_new” and also removed Date filter . If you need to add it then please do.

Regarding Second requirement, first table can only filter the second table to Location. You can apply a visual level filter on Second visual for this as done in my sample.

If there are any other issue, then request to mark this as close and raise a new post.

driver with 3 more incidents for specific cat and subcat_Ankit.pbix (257.6 KB)

Thanks
Ankit J

[Iwona]

Hi @ankit,
I primarily posted my biggest locations with many people who have those heavy incidents. The last one is the one where my boss proved me wrong so I thought it will be easy to run your measures based on that one. Please accept my apologies for the confusion made by my logic. I recreated all your measures and adjusted them for new data to process - this was the easiest way to check.

Regarding PBI file, I have noticed :

1. number of employees is incorrect , there are many more than 3
   

   

2. Somehow “Vandalism” appears there and it isn’t in the measure.

Would you please help.

Many thanks,

Iwona

[ankit]

Hi @Iwona - For 1st - It is for you to analyze why it is missing. I have already provided all the logic requested. Further, can make use of “Num Employees with Incidents > 3_Id_new” to get Employee Id’s that are included.

For 2nd - There is no filter applied for Category, Sub-Category on visual. They needs to be applied separately on visual.

That’s all the help I can provide from my side

Thanks
Ankit J

[Iwona]

@ankit

Thank you for your quick answer, effort, and your time.
Sadly, I am unable to use your measures for mentioned requirements
Somehow, it doesn’t display all employees who should be visible based on the requirements.

Regards,

Iwona

[ankit]

@Iwona . The reason you are only getting 3 records is as I have taken the sample data of around 40 Rows and not the entire dataset. That’s why I asked you to do some analysis yourself since as per me logic is correct.

Purpose of the Forum is to provide/help with the logic, that is already done. Testing side or implementation shall be done by the user only.

Do mark this post as closed or keep it open but that’s all I can help on this.

Thanks
Ankit J

[Iwona]

Hi @ankit ,

For this particular case ( given a sample of 40 records) there are 4 people, not 3 who meet the requirements which means the result is incorrect.
I don’t know why the measures don’t capture all required cases.
driver with 3 more incidents for specific cat and subcat_Ankit (2).pbix (261.4 KB)

I have added calculated columns purposely to distinguish desired categories and subcategories.

Once again, looking at 40 rows of the tab called 05_07_2023, there are 4 people ( 9820689, 9818601, 9502978, 9502018) with 3 and more incidents and they have in total of 21 incidents. Adding filter in filter pane ( last 12 completed periods didn’t help).

And still no success

I will keep it open. Maybe someone else experiences some similar issues and will share with me a different approach.

Thank you .

Iwona

[ankit]

Do keep it open and hopefully someone will help you. Like I said earlier that’s all I can help with.

[Iwona]

Hi @ankit ,

I decided to close this one and open new one with sample of 100 rows and drafted specific solution posted as well. Tis will be clear, precise and specific.

Many thanks,

Iwona

[Anonymous80]

Hi @Iwona , i took quick look at your question and i tried to implement part of it at this moment. i provided screen shots below. let me know if thats what you need so i can provide more details. thanks

[Iwona]

Hi @Anonymous80

Many thanks for taking the time to look into my problem.
I might have something like you what is here:

How and what to calculate to get proper values and correct totals?

Hi all I am trying to create another table with details on the right side but it doesn’t match any values to the table on the left side. [image] On the left side table, I am assigning a number of employees with 3+ incidents and an exact number of incidents they caused. On the right side, I am trying to display the details related to content on the left-sided table but I don’t know what to do. Would you please help? 10072323 forum - Incidents.pbix (260.0 KB) Grateful as always. Iwona

Please have a look into the attached PBI file and let me know your thoughts or feedback.

Many thanks,

Iwona