Forum Discussion
AdministratorHelp with DAX power BI
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
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:
24 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Syndicate_Adminhttps://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.
- GeraldGEmerick
Memorable Member
Syndicate_Admin I don't see an image. Sample data would be extremely helpful in trying to decipher your request.
- Syndicate_Admin
Sorry, I wanted to attach the Power BI, however, it can't.
I attach an example image of both databases so that it is understood a little.
v-hashadapuCommunity Support
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
- 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 , 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.
- 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.
