Optimizing DAX Calculation Efficiency and Resolving Memory Issues with WorkHourTemp Field

[Bubufun]

I have created a DAX field called WorkHourTemp, but the calculation process consumes too much memory. Once the data exceeds ten thousand records, it results in an 'out of memory' issue.

Here is the file link: https://drive.google.com/file/d/1sdb7dWlP6oeFJTc8DPdA6qR3rRnNNqpK/view?usp=sharing

I would like to inquire about modifications to optimize this DAX program.

Context:

The original data combines data from multiple machines into a single TABLE, recording the mV values of each machine and the timestamp of each record (in the format yyyy/M/d HH:mm:ss).

It's possible for multiple machines to run simultaneously, but due to specific settings in each machine's sensors, identical timestamps never occur.

For the purpose of understanding each machine's operational time within specific intervals, WorkHourTemp is set to calculate the operating time of a single machine under the same machine condition.

WorkHourTemp is utilized to observe the total machine operation time.

Criteria for WorkHourTemp:

Within the same machine condition, chronologically order timestamps in UTC from earliest to latest.

Events: 'clock-in' denotes someone sensing and starting the machine. 'clock-out' signifies someone sensing and stopping the machine.

WorkHourTemp = 
VAR CurrentName = [Name]
VAR CurrentEvent = [Event]
VAR CurrentmV = [mV]
VAR CurrentUTC = [UTC]

VAR PrevRowUTC =
    CALCULATE(
        MAX([UTC]),
        FILTER(
            ALL(data_1),
            [UTC] < CurrentUTC &&
            [Name] = CurrentName
        )
    )

VAR PrevRowEvent =
    IF(
        ISBLANK(PrevRowUTC),
        BLANK(),
        CALCULATE(
            MAX([Event]),
            FILTER(
                ALL(data_1),
                [UTC] = PrevRowUTC &&
                [Name] = CurrentName
            )
        )
    )

VAR PrevRowmV =
    IF(
        ISBLANK(PrevRowUTC),
        BLANK(),
        CALCULATE(
            MAX([mV]),
            FILTER(
                ALL(data_1),
                [UTC] = PrevRowUTC &&
                [Name] = CurrentName
            )
        )
    )

VAR PrevClockoutUTC =
    CALCULATE(
        MAX([UTC]),
        FILTER(
            ALL(data_1), 
            [Event] = "Clock-out" && 
            [UTC] < CurrentUTC &&
            [Name] = CurrentName)
    )

VAR PostClockoutUTC =
    CALCULATE(
        MIN([UTC]),
        FILTER(
            ALL(data_1), 
            [Event] = "Clock-out" && 
            [UTC] > CurrentUTC &&
            [Name] = CurrentName)
    )
 
VAR PreClockinUTC =
    CALCULATE(
        MAX([UTC]),
        FILTER(
            ALL(data_1), 
            [Event] = "Clock-in" && 
            [UTC] < CurrentUTC  &&
            [Name] = CurrentName)
    )

VAR PostClockinUTC =
    CALCULATE(
        MIN([UTC]),
        FILTER(
            ALL(data_1), 
            [Event] = "Clock-in" && 
            [UTC] > CurrentUTC  &&
            [Name] = CurrentName)
    )

RETURN
IF(
    ISBLANK(PrevRowUTC) ||  // Skip the calculation for the first row of each machine
    CurrentEvent = "Clock-in" || CurrentEvent = "Clock-out" ||
    PrevRowEvent = "Clock-in" || PrevRowEvent = "Clock-out" ||
    PrevRowmV >= 1,
    0,
    IF(
        PrevRowmV < 1,
        IF(OR(PostClockinUTC> CurrentUTC && CurrentUTC > PrevClockoutUTC && PostClockinUTC > PrevClockoutUTC && PostClockoutUTC > PostClockinUTC && PrevClockoutUTC > PreClockinUTC,
              ISBLANK(PrevClockoutUTC) && ISBLANK(PreClockinUTC) && PostClockinUTC> CurrentUTC && PostClockoutUTC > PostClockinUTC && PostClockoutUTC > CurrentUTC ||
              ISBLANK(PostClockinUTC) && ISBLANK(PostClockoutUTC) && CurrentUTC > PrevClockoutUTC && PrevClockoutUTC > PreClockinUTC && CurrentUTC > PrevClockoutUTC ||
              ISBLANK(PostClockoutUTC) && PostClockinUTC > CurrentUTC && CurrentUTC > PrevClockoutUTC && PrevClockoutUTC > PreClockinUTC
            ), 
        0,
        ROUND((CurrentUTC - PrevRowUTC) * 24, 2)
        ),
        BLANK()
    )
)

In this process:

• If 'clock-in' occurs before 'clock-out,' it indicates someone operating the machine.

  • If the previous mV value is less than 1 within this interval, calculate the difference in UTC between the current and previous record, in hours to two decimal places.
  • If the previous mV change is greater than or equal to 1, do not calculate the time difference.
  • If the previous record is empty, or if Event = Clock-in or Clock-out, do not calculate the time difference.
  • If the current Event = Clock-in or Clock-out, do not calculate the time difference.

• If 'clock-out' occurs before 'clock-in,' it implies no machine operation. No calculations are needed within this interval.

Example using Machine 1:

First red-box scenario:

The first red-boxed line is within the interval of 'clock-in' occurring before 'clock-out,' indicating machine operation by someone.

Once the mV value drops below 1, calculate the UTC time difference between the current and previous record.

The first data entry within the first red box indicates that Machine 1 had a manual operation starting at 2023/7/9 8:20:00 PM. Hence, WorkHourTemp is 0.

The second data entry within the first red box records an mV value below 1. The WorkHourTemp of 3.88 hours is based on the time difference between the second UTC record at 2023/7/10 12:13:00 AM.

This process repeats similarly for the second through fourth red boxes.

Blue-box scenarios represent 'clock-out' occurring before 'clock-in,' signifying no machine operation. No calculations are needed within these intervals.

The above explains how I've written WorkHourTemp.

Below is the link to related issues I encountered while utilizing the WorkHourTemp field. It aims to exclude the 'test period' from the values output by the WorkHourTemp field.

https://community.fabric.microsoft.com/t5/Desktop/Determining-Actual-Machine-Operation-Time-by-Filte...

It would be better to exclude the 'test period' within the WorkHourTemp field as described in the provided link. However, if this is not possible, the immediate priority is to rewrite the WorkHourTemp calculation to enhance efficiency and resolve memory issues.

[lbendlin]

First thing you want to do is disable auto date/time.  Your UTC column has a high cardinality as it is, and doesn't need that additional baggage.

but due to specific settings in each machine's sensors, identical timestamps never occur.

Yeah, no.

If the previous mV value is less than 1 within this interval, calculate the difference in UTC between the current and previous record, in hours to two decimal places.
If the previous mV change is greater than or equal to 1, do not calculate the time difference.
If the previous record is empty, or if Event = Clock-in or Clock-out, do not calculate the time difference.

Those are all rather ambiguous.  I don't see any empty records, for example.  What does "mV change"  mean?  Are you only ever looking for the rising slope?