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:
Administratorhttps://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
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.
- 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
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: