Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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