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:
AdministratorMy apologies, I just checked and I have left the file as public.
Super UserYour 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.
- Syndicate_Admin
Yes, the truth is that there is no end date, however, when a stop is extended to the next day the "Start date" column is also changed to the next day, so it seems to me that it is not a problem.
Yes, I can make the calendar table but my problem finally (or I think) is how to translate the logic I'm looking for into a DAX or power query (M), of sequentially checking the production database with the stops database
v-hashadapuCommunity Support
Hi Syndicate_Admin , Thank you for reaching out to the Microsoft Community Forum.
Power Query just needs a way to compare each row with the one that came before it. The easiest way is to sort your detention table, add an Index column and then merge the table with itself using that index so each row is paired with the previous one. Once the current and previous rows sit side by side, you can check whether they belong to one continuous stop, same date, same machine, same Level2 and the next start time beginning right after the previous end time. When that condition is true, you treat them as part of the same event. After that, you group those continuous blocks and collapse them into a single record with the earliest start, latest end and correct total minutes.
With those consolidated stop events, the link to the production table becomes straightforward. You merge by date and machine to narrow the search down and then keep only the production rows whose time window overlaps the detention window. That overlap is what tells you which product, squad or leader the stop belongs to.