Hot to connect two tables with different time dimensions?

[ruslan88990]

Hello, community. I have a table with production events for a machine, start\end time of event and their type  + every unique row_id for every row.(Screenshot № 1).
I also have a table with electricity consumption for this machine (Screenshot № 2). As u can see production events usually takes 15-20-30 minutes for event duration, meanwhile electricity put values +- every minute. I have a task to calculate electricity consumption per different events like stoppages and running with accurate to minute and i dont know how to deal it.
I tried formulas like these, i took rounded to one minute DateTime from Electricity table and tried to combine electricity consumption between start and end time of event but Power BI freezes until it crash. Lookupvalue also crashes Power BI. Please help me with my task. Am i doing everything wrong?

Electricity Consumption = SUMX(FILTER('Electricity','Electricity'[5001 Electricity kWh]),[Rounded DateTime] >= [start_date_local] && [Rounded DateTime] <= [end_date_local])

[rajendraongole1 · Accepted solution]

Hi @ruslan88990 -check your both tables have the DateTime columns formatted correctly.Create a relationship between the production events and electricity consumption tables, if possible, using a common key.Create a time table with every minute in the range of your data. This table will help you bridge the two datasets.

create a time table

TimeTable =
ADDCOLUMNS(
CALENDAR(MIN('Electricity'[DateTime]), MAX('Electricity'[DateTime])),
"Minute", TIME(HOUR([Date]), MINUTE([Date]), 0)
)

Create a measure to calculate electricity consumption for each production event.

Electricity Consumption =
SUMX(
FILTER(
'Electricity',
'Electricity'[Rounded DateTime] >= MIN('ProductionEvents'[start_date_local]) &&
'Electricity'[Rounded DateTime] <= MAX('ProductionEvents'[end_date_local])
),
'Electricity'[5001 Electricity kWh]
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[ruslan88990]

Hello, the formula you suggested is 1 in 1 what I wrote as a calculated column earlier, but the measure worked, yes, it takes a long time to load but it works, thank you.

Do I understand correctly that the second option is to create a table with a step of 1 minute for each next row and link through it?