Count of Single Daily Occurrence

[Zoreen]

Hello everyone, 

I am working on a report to count the single occurence of attendance of employees in the database daily.

I have The Sample Facttable below. Due to some inconsistencies in the tool, I have multiple attendace type entries on the Sample Table, for the same employee, on the same day. I want to disregard the multiple entries and "Total Time in Seconds" Column, counting each occurence for an employee, with the same "Attentance Type" name for the same day, as 1. 

The data in my table is currently stored like the table below: 

Sample FactTable

Apply Date	Total Time in Seconds	Employee name	EMP ID	Attendance Type
3/1/2023	64800	Steve Turner	0341	ABSENT NON PAID
3/1/2023	-64800	Steve Turner	0341	ABSENT NON PAID
3/1/2023	7200	Pablo Fernandez	2216	ABSENT NON PAID
3/1/2023	-1800	Pablo Fernandez	2216	ABSENT NON PAID
3/1/2023	34200	Todd Jackson	1441	SICK- NON PAID
3/1/2023	-3600	Todd Jackson	1441	SICK- NON PAID
3/1/2023	12840	Karla Andrews	2759	SICK - PAID
3/1/2023	13740	Karla Andrews	2759	SICK - PAID
3/1/2023	14640	Gabby Holier	0111	SICK - PAID
3/1/2023	15440	Gabby Holier	0111	SICK - PAID
3/1/2023	34200	Maleek Armstrong	3084	ABSENT PAID
3/1/2023	-64800	Maleek Armstrong	3084	ABSENT PAID
3/1/2023	-3600	Chris Dube	2174	ABSENT PAID
3/1/2023	7200	Chris Dube	2174	ABSENT PAID
3/1/2023	-64800	Justina Peters	1101	SICK(b4 Leave) PAID
3/1/2023	7200	Justina Peters	1101	SICK(b4 Leave) PAID
3/1/2023	34200	Cody Frankz	0089	SICK(b4 Leave) PAID
3/1/2023	-1800	Cody Frankz	0089	SICK(b4 Leave) PAID
3/1/2023	28800	Summer Taims	1557	SICK(b4 Leave) NON PAID
3/1/2023	-1800	Jeniffer North	4741	NO SHOW
3/1/2023	34200	Jeniffer North	4741	NO SHOW

I need your help to write a DAX that can count only a single occurence, for each employee, for each day, ignoring the multiple entries. I hope for the final solution for my report to look like the below: 

Attendance Report for March 1st 2023

Attendance Type	Occurrence Count
ABSENT NON PAID	2
SICK- NON PAID	1
SICK - PAID	2
ABSENT PAID	2
SICK(b4 Leave) PAID	2
SICK(b4 Leave) NON PAID	1
NO SHOW	1

Please and thank you so so much, in advance

[Greg_Deckler]

@Zoreen You can use the built-in Count (Distinct) functionality for EMP ID or this measure:

Measure = COUNTROWS(DISTINCT('Table'[EMP ID]))

[Zoreen]

Hi Greg, 

Thank you so much for your response and help with this!!!

COUNTROWS(DISTINCT('Table'[EMP ID]))

Yes, that will work for my Daily Report. To my understanding (being a newbie), if I use "DISTINCT" it removes all other entries for each employee, with the current level of detail, which is daily(ApplyDate), in my table above. However, data from March 2nd 2023 will be appended to the Sample FactTable, till the end of 2023, and going forward.

Eventually, we roll up the daily reports for the Month End and Annual Reports, using the underlying dataset. Which means, I will need to rewrite a new formular that capture the whole data entered in the FactTable, for the month and then the whole 2023, using the formular above, will not give me the desired outcome.

For example using the sample employee above, we could have a scenarios where employee "Steve Turner" was Absent, multiple times in the month of March (March 1, March 10 and March 15, total of 3). But the data was captured inconsistently, like the table above, having multiple entries for this agent on all three days.

Is there a DAX I can write, that counts just a single occurence Attendance Type, for each employee, for each day, at the end of a each given month and year.

Thanks once again, I appreciate all your help with this.