Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Zoreen
Frequent Visitor

Count of Single Daily Occurrence

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 DateTotal Time in SecondsEmployee nameEMP IDAttendance Type
3/1/202364800Steve Turner0341ABSENT NON PAID
3/1/2023-64800Steve Turner0341ABSENT NON PAID
3/1/20237200Pablo Fernandez2216ABSENT NON PAID
3/1/2023-1800Pablo Fernandez2216ABSENT NON PAID
3/1/202334200Todd Jackson1441SICK- NON PAID
3/1/2023-3600Todd Jackson1441SICK- NON PAID
3/1/202312840Karla Andrews2759SICK - PAID
3/1/202313740Karla Andrews2759SICK - PAID
3/1/202314640Gabby Holier0111SICK - PAID
3/1/202315440Gabby Holier0111SICK - PAID
3/1/202334200Maleek Armstrong3084ABSENT PAID
3/1/2023-64800Maleek Armstrong3084ABSENT PAID
3/1/2023-3600Chris Dube2174ABSENT PAID
3/1/20237200Chris Dube2174ABSENT PAID
3/1/2023-64800Justina Peters1101SICK(b4 Leave) PAID
3/1/20237200Justina Peters1101SICK(b4 Leave) PAID
3/1/202334200Cody Frankz0089SICK(b4 Leave) PAID
3/1/2023-1800Cody Frankz0089SICK(b4 Leave) PAID
3/1/202328800Summer Taims1557SICK(b4 Leave) NON PAID
3/1/2023-1800Jeniffer North4741NO SHOW
3/1/202334200Jeniffer North4741NO 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 TypeOccurrence Count
ABSENT NON PAID2
SICK- NON PAID1
SICK - PAID2
ABSENT PAID2
SICK(b4 Leave) PAID2
SICK(b4 Leave) NON PAID1
NO SHOW1

 

Please and thank you so so much, in advance

2 REPLIES 2
Greg_Deckler
Community Champion
Community Champion

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

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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.

 

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.