Help with DAX power BI

[Syndicate_Admin]

I have a detention sheet, where the order of the columns is as follows:

Column A: Start Date

Column B: Line (machine)

Columnca C: Start time (what time the arrest began)

End 😧 Time column (what time the stop ended)

Column E: Level 2 (Here the origin of the stop is specified with a drop-down list, if it is for collation, set up, operational adjustment, mechanical failure, electrical failure)

Column F and G: Level 3 and 4 (This is a more specific deployment of Level 2, where more details about the arrest are imputed)

Column H: Observation (Here they elaborate on what they may have observed about the failure)

Column I: Time in minutes (The difference basically between column D and C)

Column J: Shift (Shift 1, 2, or 3)

Column K: Lead Name

Column L: Detention Type (If it is micro detention or detention)

That in short, in addition to this table, I have a production database loaded.

This production database distributes the columns as follows:-

Column A: Date

Column B: Shift (1,2 or 3)

Column C: Leader

Machine Column 😧

Column E, F and G: Thickness, width and length respectively

Column H: Start Time

Column I: End Time

and so many other columns but those of interest are those, mainly.

Now the following:

It happens that on many occasions operators impute "set up" (level 2) and are divided into 2 imputations (image attached) As you can see, there is an imputation from "11:54:48" to "12:01:04" and then another one that starts often at "12:01:15" until "12:59:00". So, that which is divided into 2 rows, I want a formula that can recognize those failures and add both times (column J in this case or column "time in minutes" for power bi) that when these 2 events occur and are consecutive in time they are considered as 1, can something like this be done?

In addition, with respect to the same, within the detention form, in level 2, "obstruction of piece" is specified

I want to be able to relate both "database" and "arrests" to be able to obtain the data of the participation of that arrest by product or squad (there is a column in the database called squad) then, as the dates are in the arrest table, it would be necessary to detect when "date, machine, and times" coincide to associate it

[v-hashadapu]

Hi @Syndicate_Admin , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

[v-hashadapu]

Hi @Syndicate_Admin , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[Syndicate_Admin]

---

@Syndicate_Admin wrote:

Hello @Syndicate_Admin , I hope you're okay! Can we tell if it worked for you or are you still struggling? Tell us: your feedback can help others in the same situation a lot.


Hello, I have modified and applied the code in M language that a colleague gave me which is the following:

Let me
// 1. Reference to your original table
Base = Arrests,

// 2. Create combined Date and Time columns
AddStartDateTime = Table.AddColumn(
Base,
"StartDateTime",
each DateTime.From([#"Start Date"]) + Duration.From([#"Start Time"]),
type datetime
),
AddEndDateTime = Table.AddColumn(
AddStartDateTime,
"EndDateTime",
each DateTime.From([#"Start Date"]) + Duration.From([#"End Time"]),
type datetime
),

// 3. Sort Data (Vital for Previous Row Logic)
SortedRows = Table.Sort(
AddEndDateTime,
{
{"Start Date", Order.Ascending},
{"Line", Order.Ascending},
{"StartDateTime", Order.Ascending}
}
),

// 4. Add Table of Contents to compare with the previous row
AddIndex = Table.AddIndexColumn(SortedRows, "Index", 0, 1, Int64.Type),

// 5. Create the Group ID using a custom function to avoid slow "Join"
This part identifies whether the current row belongs to the previous group or is a new one
AddGroupFlag = Table.AddColumn(AddIndex, "NewGroupFlag", each
Let me
CurrentRow = AddIndex{[Index]},
PreviousRow = if [Index] > 0 then AddIndex{[Index]-1} else null,
IsNewGroup =
if PreviousRow = null then 1
else if CurrentRow[Line] <> PreviousRow[Line] then 1
else if CurrentRow[#"Start Date"] <> PreviousRow[#"Start Date"] then 1
else if CurrentRow[#"Level 2"] <> PreviousRow[#"Level 2"] then 1
else if Duration.TotalSeconds(CurrentRow[StartDateTime] - PreviousRow[EndDateTime]) > 30 then 1
else 0
in
IsNewGroup, Int64.Type
),

// 6. Create a Running Total ID for Flags
This groups consecutive rows under a single ID
AddGroupID = Table.AddColumn(AddGroupFlag, "GroupID", each List.Sum(List.FirstN(AddGroupFlag[NewGroupFlag], [Index] + 1)), Int64.Type),

// 7. Group by GroupID
GroupedStops = Table.Group(
AddGroupID,
{"GroupID", "Start Date", "Line", "Level 2"},
{
{"Start Time", each List.Min([#"Start Time"]), type time},
{"End Time", each List.Max([#"End Time"]), type time},
{"Time in Minutes", each List.Sum([#"Time in Minutes"]), type number}
}
),

Final cleanup: Remove the group ID column if you don't need it
RemovedGroupID = Table.RemoveColumns(GroupedStops,{"GroupID"})
in
RemovedGroupID

However, I have not been successful, I do not know if I translated the code correctly so that it is used correctly. I remain attentive

---

[v-hashadapu]

Hi @Syndicate_Admin , can you confirm if you tried the suggestions in my last reply on 17th December? that would help us understand the issue.

[Syndicate_Admin]

The code I sent was what I used that I think is what you have suggested, without much success (if I had translated the code well since it was not arrive and paste in my case)

[v-hashadapu]

Hi @Syndicate_Admin , Thank you for reaching out to the Microsoft Community Forum.

Since your report uses two data sources that we cannot access, we’re unable to reproduce the scenario successfully. Based on the details you shared, we suggested a solution using M code, but if that approach is not working for you, we need sample data to investigate further.

To proceed, please provide two separate Excel files that we can download and use as data sources. Include only the columns and rows required to demonstrate the issue and exclude anything confidential. Please also show what the expected outcome should be based on this sample data.

[Syndicate_Admin]

https://drive.google.com/drive/folders/1QAOyxxynKT1_dznhEGEUgz2olknCJ6J1?usp=drive_link

Generate this folder in drive with all the files to be used (no confidential information exists)

Now, I upload two example images as to what I'm looking for (they're just examples)
It happens that on several occasions I have imputations (of different types) which are cut and generate two "inputs" or inputs to which I would like to include a way that in its logic can join these imputations into a single one if it detects for example that it is a continuous failure (first case goes from 21:32 to 22:40, then from 22:40 to 23:06 but it was not imputed as set up and so on until we reach again an imputation that is typed as set up at 23:10) So in principle I don't know if you can make a kind of sandwich in those cases when I detect a linearity in the data.
The second scenario shows two continuous detentions separated by only a couple of minutes but it is the same imputation, the desire is the same... that, for example, if there is a difference of 5 minutes (at most) between each detention that unifies them, this criterion will be passed that they remain separate.

Finally, another goal I have with this is to be able to relate the database to the arrests. As you can see in all productions there are hours as in the stop sheet, so depending on the stop time I want you to identify in the other sheet the SKU that was being produced in that time interval.

I remain attentive to your comments, thank you very much in advance.

Best regards

[v-hashadapu]

Hi @Syndicate_Admin , Thank you for reaching out to the Microsoft Community Forum.

I created sample data based on your Excel files to validate the end-to-end logic. The Detenciones_Limpias Power Query (M) logic merges consecutive stoppage records into unified time blocks per production line using a configurable time-gap tolerance, correctly handles events that cross midnight and consolidates stoppage intervals with accurate duration calculations. Detenciones_Final aggregates the cleaned records into a single row per time block, per line, per day. It computes precise start and end timestamps, total duration, assigns a consolidated block reason and standardizes the machine identifier to ensure consistency for downstream analysis.

Produccion_DT converts the production date and time fields into accurate start and end datetimes, including proper handling of overnight shifts and standardizes the machine identifier to enable reliable joins with detention data. Finally, Detenciones_Con_Produccion aligns each detention event with overlapping production time windows, calculates the overlap duration in minutes and allocates those minutes into the appropriate detention type buckets (such as SET UP, AJUSTE, and others).

Please refer to the attached sample data and the provided .PBIX file for the complete implementation and validation.

[Syndicate_Admin]

Thank you very much for your attendance, I will comment point by point.

In "Detenciones_Limpias" I compare with the real spreadsheet and indeed I see that it merges times but they do not match the base sheet. (I will leave a reference image)
Regarding "Detenciones_Final" The phenomenon is the same. In any case, in some cases it is applied well but the result is not the desired one, as seen in the previous example or in the following one:

He ended up adding 30 minutes of snack that was skipped.

Finally, in "Detenciones_Con_Producción" I see that you have added columns that come from the production database, however, those added data do not represent much use to me. What I wanted to retrieve from the database was the SKU or squad so that I could associate the arrest with a particular product.

[v-hashadapu · Accepted solution]

Hi @Syndicate_Admin , Thank you for reaching out to the Microsoft Community Forum.

Please refer below sample spreadsheet snap:

Detenciones_Limpias snap:

Detenciones_Con_Producción snap:

Please refer attached .pbix file and output snaps and share your thoughts:

[v-hashadapu]

Hi @Syndicate_Admin , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[Syndicate_Admin]

I still can't figure out how to transfer the logic they tell me about to a power query or DAX, I've tried but without much success.

[v-hashadapu]

Hi , Thank you for reaching out to the Microsoft Community Forum.

You are connecting to Detention sheet(data source1) and Production database distributes (data source2), i tried to replicate the scenario, but getting the below error, due to "DataSource.NotFound".

I can't connect to your data source from my end. Please refer below M code. and follow below steps.

1. In power desktop--> Query editor.

2. select New Source --> Blank Query --> In advanced editor.

Remove everything and paste the below code:

let

    // BASE TABLE (reference existing Detenciones query)

    Base = Detenciones,

    // CREATE DATETIME COLUMNS

    AddStartDateTime = Table.AddColumn(
        Base,
        "StartDateTime",
        each DateTime.From([#"Fecha de Inicio"]) 
            + Duration.From([#"Hora de Inicio"]),
        type datetime
    ),

    AddEndDateTime = Table.AddColumn(
        AddStartDateTime,
        "EndDateTime",
        each DateTime.From([#"Fecha de Inicio"]) 
            + Duration.From([#"Hora de Finalización"]),
        type datetime
    ),

    // SORT DATA (CRITICAL FOR SEQUENTIAL LOGIC)

    SortedRows = Table.Sort(
        AddEndDateTime,
        {
            {#"Fecha de Inicio", Order.Ascending},
            {#"Línea", Order.Ascending},
            {"StartDateTime", Order.Ascending}
        }
    ),

    // ADD INDEX

    AddIndex = Table.AddIndexColumn(
        SortedRows,
        "Index",
        0,
        1,
        Int64.Type
    ),

    // SELF-JOIN TO PREVIOUS ROW

    JoinPrevious = Table.NestedJoin(
        AddIndex,
        {"Index"},
        AddIndex,
        {"Index"},
        "PrevRow",
        JoinKind.LeftOuter
    ),

    ExpandPrev = Table.ExpandTableColumn(
        JoinPrevious,
        "PrevRow",
        {"Línea", "Fecha de Inicio", "Nivel 2", "EndDateTime"},
        {"PrevLínea", "PrevFecha", "PrevNivel2", "PrevEndDateTime"}
    ),

    // FLAG NEW GROUP

    AddNewGroupFlag = Table.AddColumn(
        ExpandPrev,
        "NewGroup",
        each
            if [PrevLínea] = null then 1
            else if [#"Línea"] <> [PrevLínea] then 1
            else if [#"Fecha de Inicio"] <> [PrevFecha] then 1
            else if [#"Nivel 2"] <> [PrevNivel2] then 1
            else if Duration.TotalSeconds(
                    [StartDateTime] - [PrevEndDateTime]
                 ) > 30 then 1
            else 0,
        Int64.Type
    ),

    // CREATE GROUP ID (RUNNING TOTAL)

    AddGroupID = Table.AddColumn(
        AddNewGroupFlag,
        "GroupID",
        each
            List.Sum(
                List.FirstN(
                    AddNewGroupFlag[NewGroup],
                    [Index] + 1
                )
            ),
        Int64.Type
    ),

    // GROUP CONSECUTIVE DETENTIONS

    GroupedDetentions = Table.Group(
        AddGroupID,
        {"GroupID", #"Fecha de Inicio", #"Línea", #"Nivel 2"},
        {
            {
                "Hora de Inicio",
                each Time.From(
                    DateTime.Time(
                        List.Min([StartDateTime])
                    )
                ),
                type time
            },
            {
                "Hora de Finalización",
                each Time.From(
                    DateTime.Time(
                        List.Max([EndDateTime])
                    )
                ),
                type time
            },
            {
                "Tiempo en Minutos",
                each List.Sum([#"Tiempo en Minutos"]),
                type number
            }
        }
    )

in
    GroupedDetentions

I hope this information helps. Please do let us know if you have any further queries.

[v-hashadapu]

Hi @Syndicate_Admin , Thank you for reaching out to the Microsoft Community Forum.

The key is to treat those split set up rows as one continuous stop whenever the second row begins right after the first one ends. The most reliable way is to handle this in Power Query, sort the detention table by date, machine, Level 2 and start time, then merge any rows within each group whose start time follows immediately after the previous end time. That produces a single consolidated event with one start, one end and one total duration, instead of two separate entries.

With those consolidated events in place, you can then relate detentions to the production database by using time overlap rather than matching single timestamps. For each detention event, look for production rows on the same date and machine where the production interval overlaps the detention interval. That gives you the correct squad, product or leader associated with that stop.

[Syndicate_Admin]

Yes, that's how I saw it.. However, I didn't know how to translate it into the M language of power query to be able to establish that relationship. Could you help me with it, please

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Syndicate_Admin]

https://drive.google.com/file/d/1VQxu75c2BTS5lk_Jti99BGLrE7p8WN5B/view?usp=drive_link

There is the PBI file mentioned, thanks for the suggestions

[lbendlin]

The link requires access. please check.

[Syndicate_Admin]

My apologies, I just checked and I have left the file as public.

[lbendlin]

Your data seems to be missing the End Date column - how are you planning to handle events that run across midnight?

You may also want to invest in a calendar table.

Identifying the "previous" record is expensive in your scenario (multi million row fact table) and will likely have to be done outside of Power BI. For example with an index column in Power Query.