Count of ID With duplicate status

[tejutulasi67]

Hello Everyone,
i have large attendance Database. I want to calculate how many present/absent every day.
but few IDs are having duplicate values as shown below. so the same id count is showing in present and absent also.
i have total X number of head count. for X headcount present must be some Y and Absent must be X-Y. But my Absent plus Present count is more than Headcount due to the duplicate Records. So please Help.

[v-jianboli-msft · Accepted solution]

Hi @tejutulasi67 ,

Based on your description, I have created a simple sample:

Please try:

Status = 
var _a = SUMMARIZE('Table',[Employee ID],[In Time],[Out Time],"Hours Worked",DATEDIFF([In Time],[Out Time],HOUR))
return IF(SUMX(_a,[Hours Worked])<>0,"Present","Absent")

Count of Absent = 
var _a = SUMMARIZE('Table',[Employee ID],[In Time],[Out Time],"Hours Worked",DATEDIFF([In Time],[Out Time],HOUR))
var _b = SUMMARIZE(_a,'Table'[Employee ID],"Hours worked",SUMX(FILTER(_a,[Employee ID]=EARLIER('Table'[Employee ID])),[Hours Worked]))
return COUNTX(FILTER(_b,[Hours worked]=0),[Employee ID])

Count of Present = 
var _a = SUMMARIZE('Table',[Employee ID],[In Time],[Out Time],"Hours Worked",DATEDIFF([In Time],[Out Time],HOUR))
var _b = SUMMARIZE(_a,'Table'[Employee ID],"Hours worked",SUMX(FILTER(_a,[Employee ID]=EARLIER('Table'[Employee ID])),[Hours Worked]))
return COUNTX(FILTER(_b,[Hours worked]<>0),[Employee ID])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@tejutulasi67 , what should it be counted when both are there? present and absent ?

[tejutulasi67]

I have punch in time out time and no of hours worked fields also as shown below. if the person has working hours then present should come otherwise absent.

thank you.