How to calculate days worked

[yiying]

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? 

[v-jiascu-msft · Accepted solution]

Hi @yiying,

Try out this measure please.

Measure =
COUNTX (
    SUMMARIZE ( 'Complete WO', 'Complete WO'[Full Name], 'Complete WO'[Datekey] ),
    [Datekey]
)

Best Regards,

Dale

[v-jiascu-msft]

Hi @yiying,

Could you please mark the proper answer as solution?

Best Regards!
Dale

[Ashish_Mathur · Accepted solution]

Hi,

This measure gives 12 as the answer

=COUNTROWS(SUMMARIZE(Data,Data[Full Name],Data[Date]))

Hope this helps.

[v-jiascu-msft · Accepted solution]

Hi @yiying,

Try out this measure please.

Measure =
COUNTX (
    SUMMARIZE ( 'Complete WO', 'Complete WO'[Full Name], 'Complete WO'[Datekey] ),
    [Datekey]
)

Best Regards,

Dale

[chethan]

@yiying Bro

If the Person is visiting two Times, there will be the time stamp too. So separate the date only & use the Distance count. 

I will work,

Regards,

Chetan K

[jthomson]

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.