Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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]
)
Hi,
Try this
=IF(HASONEVALUE('Syslog SystemEvents[Name]),[your measure],SUMX(SUMMARIZE(VALUES('Syslog SystemEvents[Name]),[Name],"ABCD",[your measure]),[ABCD]))
Hope this helps.
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
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.
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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |