Forum Discussion
AdministratorHelp with DAX power BI
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:
Community SupportHi 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.
AdministratorThank 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
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: