Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ChrisCollao
Frequent Visitor

Count machinery splitted stops

Hi all,

 

I have this sort of data:

 

MachineStart datetimeEnd datetime
Machine12023-12-01 13:00:002023-12-02 00:00:00
Machine22023-12-02 11:00:002023-12-02 15:00:00
Machine12023-12-02 00:00:00 2023-12-03 00:00:00
Machine22023-12-03 12:00:002023-12-03 18:00:00
Machine12023-12-03 00:00:002023-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:

 

MachineStart datetimeEnd datetimeNew Detention
Machine12023-12-01 13:00:002023-12-02 00:00:001
Machine22023-12-02 11:00:002023-12-02 15:00:001
Machine12023-12-02 00:00:00 2023-12-03 00:00:000
Machine22023-12-03 12:00:002023-12-03 18:00:001
Machine12023-12-03 00:00:002023-12-03 14:00:000

 

Thanks in advance. Best regards.

1 ACCEPTED SOLUTION

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.

View solution in original post

3 REPLIES 3
Fowmy
Super User
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

 

 

Fowmy_1-1702927169109.png

 

 

 



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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.