DAX - Find sum of users clocked in without clocking out

[nizukan]

I am Trying to calculate how many people are currently in a building. When someone enters they will clock in and clock out on exit. 
I am searching for a Dax formula to find the count of all people who have clocked in the past 12 hours and did not clock out yet.

I tried doing this by finding each users last clock in w/o clocking out and is less than 12 hours. Howeve I can't seem to get a sum of the measure.

[Ashish_Mathur]

Hi,

Try this

=IF(HASONEVALUE('Syslog SystemEvents[Name]),[your measure],SUMX(SUMMARIZE(VALUES('Syslog SystemEvents[Name]),[Name],"ABCD",[your measure]),[ABCD]))

Hope this helps.

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

[nizukan]

Thanks I understand know why  that the total is being evalutated just for that total row.

I am still trying to figure out how to write a measure to gives me the users currently clocked in but not clocked out yet.

I am trying to count the rows of users who have whos latest recent  entry is greater than their exit.

Any help is appreciated.

[CanadaMGB · Accepted solution]

I think you would need to summarize your table before being able to count the rows you're looking for. For a table like this:

you could get a count of people who've clocked in but have not clocked out by using something like this as a measure:

HEADCOUNT = SUMX(
 SUMMARIZECOLUMNS(Table1[Person],
 "STILLINBUILDING",IF(CALCULATE(MAX(Table1[Time]),Table1[Type]="In")-CALCULATE(MAX(Table1[Time]),Table1[Type]="Out") > 0, 1, 0)),
 [STILLINBUILDING]
 )