Forum Discussion
Calculate maximum time with grouping earlier rows
Hello, everyone!
I stucked with writing a DAX formula that I can't manage to during the week.
I have a table that contains data about guests visited our building (with electronic cards. Also we have several buildings).
Guests come inside and leave the building only in groups, so no one new guest come in untill last guest from previous group leave. But there is no any GroupID or something specific that appropriate Guest with Group. And this is complicate the task.
Data looks like this:
BuildingID GuestID BuildingEnterTime BuildingExitTime
1 Guest1 11:05 11:15
1 Guest2 11:06 11:15
1 Guest3 11:07 11:17
1 Guest4 11:07 11:16
1 Guest5 11:07 11:18
1 Guest6 11:08 11:18
1 Guest7 11:20 11:31
1 Guest8 11:20 11:31
1 Guest9 11:22 11:32
1 Guest10 11:23 11:31
I need to create column that will display maximum exit time for a group.
How I tried to manage it:
LastExitTime = CALCULATE(MAX('Building visits'[BuildingExitTime]),FILTER('Building visits','Building visits'[Building enter date] = TODAY()&&'Building visits'[BuildingID]=EARLIER('Building visits'[BuildingID])&&'Building visits'[BuildingEnterTime]>EARLIER('Building visits'[BuildingExitTime])))
By this formula I try to loop through the current day, group Guests by BuildingID and compare that current BuildingEnterTime is greater than earlier BuildingExitTime. But actually I need to compare that current BuildingEnterTime is greater than all previous BuildingExitTime for the building. And this will be an attribute of a new group.
This formula works, but show me the maximum BuildingExitTime for the whole day for specific Building:
BuildingID GuestID BuildingEnterTime BuildingExitTime LastExitTime
1 Guest1 11:05 11:15 11:32
1 Guest2 11:06 11:15 11:32
1 Guest3 11:07 11:17 11:32
1 Guest4 11:07 11:16 11:32
1 Guest5 11:07 11:18 11:32
1 Guest6 11:08 11:18 11:32
1 Guest7 11:20 11:31 11:32
1 Guest8 11:20 11:31 11:32
1 Guest9 11:22 11:32 11:32
1 Guest10 11:23 11:31 11:32
But I want to get this:
BuildingID GuestID BuildingEnterTime BuildingExitTime LastExitTime
1 Guest1 11:05 11:15 11:18
1 Guest2 11:06 11:15 11:18
1 Guest3 11:07 11:17 11:18
1 Guest4 11:07 11:16 11:18
1 Guest5 11:07 11:18 11:18
1 Guest6 11:08 11:18 11:18
1 Guest7 11:20 11:31 11:32
1 Guest8 11:20 11:31 11:32
1 Guest9 11:22 11:32 11:32
1 Guest10 11:23 11:31 11:32
Please, give me a piece of advice about what I missed in my formula and how I can make it work. My head is going to burst soon:)
Thank you in advance!
16 Replies
- Vvelarde
Community Champion
hello mailwin33
Try to solve with this
LastExitTime = VAR ExitTime = VALUES ( Table1[BuildingExitTime] ) RETURN CALCULATE ( MAX ( Table1[BuildingExitTime] ), FILTER ( ALLEXCEPT ( Table1; Table1[BuildingID]; Table1[EnterDate] ), Table1[BuildingEnterTime] < ExitTime && MAX ( Table1[EnterDate] ) = TODAY () ) )Hi, Vvelarde.
Thank you for the reply, it's great.
I tried it and receive the error:
"A table of multiple values was supplied where a single value was expected"
I suspect that the error generates by this row:
Table1[BuildingEnterTime] < ExitTime
because ExitTime is a table with many rows (please correct me if I wrong).
But I feel that it's the right direction:)
Any further suggestions?
