Forum Discussion

Frequent Visitor

9 years ago

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 hav...

Icon for Community Champion rank

Community Champion

9 years ago

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

Vvelarde's formula is for a MEASURE

Frequent Visitor

9 years ago

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
Community Champion
9 years ago
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.

Icon for Community Champion rank

Community Champion

9 years ago

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
Frequent Visitor
9 years ago
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.