cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Count machinery splitted stops

Hi all,

I have this sort of data:

 Machine Start datetime End datetime Machine1 2023-12-01 13:00:00 2023-12-02 00:00:00 Machine2 2023-12-02 11:00:00 2023-12-02 15:00:00 Machine1 2023-12-02 00:00:00 2023-12-03 00:00:00 Machine2 2023-12-03 12:00:00 2023-12-03 18:00:00 Machine1 2023-12-03 00:00:00 2023-12-03 14:00:00

The data shows machinary stops splitted by day turns. So, if a detention lenght more than a day, it will be splitted in several rows, but it's only one detention.

I need to create a column where I can mark a 1 if the detention is new or 0 if it's an old one. The way I can detect if the detention is an old one is by comparing the start time and the end time of the last detention, if they are equal, then the stop is an old one.

How can I do this?

The result for the previous example should be:

 Machine Start datetime End datetime New Detention Machine1 2023-12-01 13:00:00 2023-12-02 00:00:00 1 Machine2 2023-12-02 11:00:00 2023-12-02 15:00:00 1 Machine1 2023-12-02 00:00:00 2023-12-03 00:00:00 0 Machine2 2023-12-03 12:00:00 2023-12-03 18:00:00 1 Machine1 2023-12-03 00:00:00 2023-12-03 14:00:00 0

1 ACCEPTED SOLUTION
Frequent Visitor
###### Sorry mom, I used GPT.

I used GPT to see another solution and to not use an iterative DAX formula. This was the result:

`ColumnaMarcador = VAR MaquinaActual = Detenciones[Maquina] VAR FechaHoraInicioActual = Detenciones[FechaHoraInicio] VAR FechaHoraFinAnterior = MAXX(     FILTER(           Detenciones,           Detenciones[Maquina] = MaquinaActual &&           Detenciones[FechaHoraFin] < FechaHoraInicioActual           ),     Detenciones[FechaHoraFin]     ) RETURN     IF(       NOT ISBLANK(FechaHoraFinAnterior) &&       FechaHoraFinAnterior = FechaHoraInicioActual,       1,       0    )`

It's amazing that the first GPT solution was pretty similar to the one presented by @Fowmy, but with the instruction of the out of memory error it managed it to make a non interative solution.

If anyone needs the prompt and chat (in spanish), here is: https://chat.openai.com/share/cc6d9a4f-9169-410c-b89c-08f61eeb44cb

Best regards.

3 REPLIES 3
Super User

@ChrisCollao
I added a calculated column :

``````New Detention =
VAR __Start = Table01[Start datetime]
VAR __PrevEnd =
CALCULATE(
MAX( Table01[End datetime] ),
ALLEXCEPT( Table01 , Table01[Machine] ),
Table01[End datetime] <= __Start
)
VAR __Result =  INT(__Start <> __PrevEnd)
RETURN
__Result``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Frequent Visitor

Hi @Fowmy

Thanks a lot. I tested it with the sample and it works. But, in my model there's more than 300.000 rows and 15 different machines. The column can't be calculated due to "insufficient memory error". Do you know if there's any walkaround we could take?

Thanks a lot.

Frequent Visitor
###### Sorry mom, I used GPT.

I used GPT to see another solution and to not use an iterative DAX formula. This was the result:

`ColumnaMarcador = VAR MaquinaActual = Detenciones[Maquina] VAR FechaHoraInicioActual = Detenciones[FechaHoraInicio] VAR FechaHoraFinAnterior = MAXX(     FILTER(           Detenciones,           Detenciones[Maquina] = MaquinaActual &&           Detenciones[FechaHoraFin] < FechaHoraInicioActual           ),     Detenciones[FechaHoraFin]     ) RETURN     IF(       NOT ISBLANK(FechaHoraFinAnterior) &&       FechaHoraFinAnterior = FechaHoraInicioActual,       1,       0    )`

It's amazing that the first GPT solution was pretty similar to the one presented by @Fowmy, but with the instruction of the out of memory error it managed it to make a non interative solution.

If anyone needs the prompt and chat (in spanish), here is: https://chat.openai.com/share/cc6d9a4f-9169-410c-b89c-08f61eeb44cb

Best regards.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors