Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
@Zoreen You can use the built-in Count (Distinct) functionality for EMP ID or this measure:
Measure = COUNTROWS(DISTINCT('Table'[EMP ID]))
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.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
9 | |
6 |