Forum Discussion
Helper IHow to calculate days worked
So here is my data
| Full Name | Date | Days Worked |
| ANTHONY | 3/1/2018 0:00 | 1 |
| ANTHONY | 3/3/2018 0:00 | 1 |
| ANTHONY | 3/6/2018 0:00 | 1 |
| ANTHONY | 3/7/2018 0:00 | 1 |
| ANTHONY | 3/7/2018 0:00 | 1 |
| ANTHONY | 3/8/2018 0:00 | 1 |
| GRANT | 3/1/2018 0:00 | 1 |
| GRANT | 3/2/2018 0:00 | 1 |
| GRANT | 3/2/2018 0:00 | 1 |
| GRANT | 3/3/2018 0:00 | 1 |
| GRANT | 3/5/2018 0:00 | 1 |
| GRANT | 3/6/2018 0:00 | 1 |
| GRANT | 3/8/2018 0:00 | 1 |
| GRANT | 3/8/2018 0:00 | 1 |
| GRANT | 3/9/2018 0:00 | 1 |
| GRANT | 3/9/2018 0:00 | 1
|
The duplicate date means that person have 2 activity that day.
I write a DAX for my measure like this :
Days Worked =
SUMX(VALUES('Complete WO'[Tech ID]),DISTINCTCOUNT('Complete WO'[DateKey]))
The duplicate date means that person have 2 activity that day.
It works fine when I filter down to one person. However in this table, it always give me 16 instead of 12.
How should I write my DAX correctly?
Hi yiying,
Try out this measure please.
Measure = COUNTX ( SUMMARIZE ( 'Complete WO', 'Complete WO'[Full Name], 'Complete WO'[Datekey] ), [Datekey] )Best Regards,
Dale
Hi,
This measure gives 12 as the answer
=COUNTROWS(SUMMARIZE(Data,Data[Full Name],Data[Date]))
Hope this helps.
6 Replies
- Ashish_Mathur
Super User
Hi,
This measure gives 12 as the answer
=COUNTROWS(SUMMARIZE(Data,Data[Full Name],Data[Date]))
Hope this helps.
v-jiascu-msftMicrosoft Employee
Hi yiying,
Try out this measure please.
Measure = COUNTX ( SUMMARIZE ( 'Complete WO', 'Complete WO'[Full Name], 'Complete WO'[Datekey] ), [Datekey] )Best Regards,
Dale
jthomsonSolution Sage
Is there more data in your table that you need to retain outside of these fields you list, or could you not look at the full name and date fields and remove duplicates?
- yiying
I need to keep those data. That duplicate means that person have 2 activity that day. Sorry about the confusion.
- v-jiascu-msft
