Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 |
Thanks in advance. Best regards.
Solved! Go to Solution.
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.
@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
⭕ 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.
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.
User | Count |
---|---|
131 | |
74 | |
70 | |
58 | |
53 |
User | Count |
---|---|
190 | |
97 | |
67 | |
62 | |
54 |