Calculate maximum time with grouping earlier rows

[mailwin33]

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!

[Vvelarde]

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 ()
        )
    )

[mailwin33]

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?

[Vvelarde]

@mailwin33

Ok, the fail is with totals of the tables. If you disabled this works?. do you need the totals?

[mailwin33]

@Vvelarde

I'm not sure that I need totals, but I don't understand where is it.

How can I disable it?

[Vvelarde]

Totals OFF

[mailwin33]

@Vvelarde

Unfortunatelly it still return the same error.

Altough I set Totals to OFF.

Maybe it bacause in this table I actually have other buildings, not only building1?

[Vvelarde]

@mailwin33

Change Max instead of Values in Var declaration

[mailwin33]

@Vvelarde

With MAX instead VALUES works, but display max BuildingExitTime for the day (as my formula did), not for the each group.

[Vvelarde]

@mailwin33

Are you using a measure to this?.

Look i change the values for Max and still work

[mailwin33]

@Vvelarde

Hmm, now I tried to create MEASURE again and it only rolls the load indicator and extremely consume my CPU.

Actually my table contains almost 1 million rows. I set the EnterDate filter for today, but data still loading.

Can I accelerate the request? 

[Sean]

@mailwin33 I suspect you are creating a COLUMN by the error you get

@Vvelarde's formula is for a MEASURE

[mailwin33]

Sorry, it looks like I disappointed you.
Measure seems to work (at least works different).
Formula accepted successfully but when I try to add this measure to report it begins to roll the loading indicator and extremely consuming CPU, although ALLEXCEPT specified Enter Date and filter is set for today.

[Vvelarde]

@mailwin33

let me check i if could modify the measure to run faster. If have some result i posted. 

The best option would be share your pbix with anonymize sensitive data and work on it.

[Sean]

@mailwin33 Try this as a COLUMN - no MAX and no VALUES

LastExitTime COLUMN = 
VAR ExitTime = BuildingVisits[BuildingExitTime]
RETURN
    CALCULATE (
        MAX ( BuildingVisits[BuildingExitTime] ),
        FILTER (
            ALLEXCEPT ( BuildingVisits, BuildingVisits[BuildingID], BuildingVisits[EnterDate] ),
            BuildingVisits[BuildingEnterTime] < ExitTime
                && MAX ( BuildingVisits[EnterDate] ) = TODAY ()
        )
    )

If you want this calculated only for TODAY( ) try this as a COLUMN as well

LastExitTime COLUMN 2 = 
IF (
    BuildingVisits[EnterDate] = TODAY (),
    VAR ExitTime = BuildingVisits[BuildingExitTime]
    RETURN
        CALCULATE (
            MAX ( BuildingVisits[BuildingExitTime] ),
            FILTER (
                ALLEXCEPT ( BuildingVisits, BuildingVisits[BuildingID] ),
                BuildingVisits[BuildingEnterTime] < ExitTime
            )
        )
)

[mailwin33]

@Sean

Thank you for the suggestion.

I believe it should works, but in my case it stucks on "Working on it" (both formulas), likely because of lot of rows, trying to loop throuh all of them, regardless I set a filter on EnterDate and use TODAY() in formula

@Vvelarde

Ok, thank you.

I'll think about how to anonimize my data and share it.

[mailwin33]

@Sean

Thank you for the reply.

Yes, I tried to create a COLUMN.

If I try to create a MEASURE it works, but display maximum BuildingExitTime for the day, not for the each group