topic Re: split event row into multiple rows by hour in DAX Commands and Tips

split event row into multiple rows by hour

xbillx81 — Sun, 04 Dec 2022 12:02:53 GMT

Need to calculate the number of minutes in each hour block a device was in error state. I gather from research this could be done with DAX calculate or generateseries but struggling for how to implement.

https://1drv.ms/u/s!Ajwjsn7bUmgJwB5St9WqA8rimPTa?e=wpA7dc <pbix file is here

Source data

DeviceID	Event Start	Event End	Category
ABC123	12/1/2022 1:00	12/1/2022 20:00	Cat1
CDE456	11/28/2022 17:48	11/29/2022 3:24	Cat2

Expected Output

DeviceID	Event Start	Event End	Category	Duration_Minutes	Percent
ABC123	12/1/2022 2:00	12/1/2022 3:00	Cat1	60	100%
ABC123	12/1/2022 3:00	12/1/2022 4:00	Cat1	60	100%
…
CDE456	11/28/2022 17:00	11/28/2022 18:00	Cat2	12	20%
CDE456	11/28/2022 18:00	11/28/2022 19:00	Cat2	60	100%
CDE456	11/28/2022 19:00	11/28/2022 20:00	Cat2	60	100%
…
CDE456	11/29/2022 3:00	11/29/2022 4:00	Cat2	24	40%

Re: split event row into multiple rows by hour

tamerj1 — Sun, 04 Dec 2022 17:02:17 GMT

Hi xbillx81
Please refer to attached sample file with the solution

Time = VAR StartTime = DATEVALUE ( MIN ( 'Table'[Event Start] ) ) VAR EndTime = DATEVALUE ( MAX ( 'Table'[Event End] ) ) + TIME ( 23, 0, 0 ) RETURN GENERATE ( SELECTCOLUMNS ( GENERATESERIES ( StartTime, EndTime, TIME ( 1, 0, 0 ) ), "Start Time", [Value] ), ROW ( "End Time", [Start Time] + TIME ( 1, 0, 0 ) ) )

Duration = VAR EventStart = SELECTEDVALUE ( 'Table'[Event Start] ) VAR EventEnd = SELECTEDVALUE ( 'Table'[Event End] ) VAR StartTime = SELECTEDVALUE ( 'Time'[Start Time] ) VAR EndTime = SELECTEDVALUE ( 'Time'[End Time] ) VAR Result = DATEDIFF ( MAX ( EventStart, StartTime ), MIN ( EventEnd, EndTime ), MINUTE ) RETURN IF ( Result > 0, Result )

% Duration = [Duration]/60

Re: split event row into multiple rows by hour

xbillx81 — Mon, 05 Dec 2022 12:40:35 GMT

This is great, thank you. Simpler than I thought, I was over complicating it.

Re: split event row into multiple rows by hour

xbillx81 — Thu, 29 Dec 2022 17:17:20 GMT

Ok i've spent a few weeks working with this and the way this displays in a table report is perfect. however, i need to perform more calculations and analysis and joins on that table and I cannot for the life of me get it to export or create into a new table. New question is how can I get the expect output into a table instead of just into the table view report. I'm working with a about 300k rows and the report is hitting max row limits.

Re: split event row into multiple rows by hour

tamerj1 — Fri, 30 Dec 2022 06:58:21 GMT

Hi xbillx81
Please refer to attached updated sample file

Table 2 = ADDCOLUMNS ( SELECTCOLUMNS ( GENERATE ( 'Table', VAR DeviceCategoryTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[DeviceID] ) ) VAR EventStart = MINX ( DeviceCategoryTable, 'Table'[Event Start] ) VAR EventEnd = MAXX ( DeviceCategoryTable, 'Table'[Event End] ) VAR StartMinute = MINUTE ( EventStart ) VAR StartTime = EventStart - TIME ( 0, StartMinute, 0 ) VAR EndMinute = MINUTE ( EventEnd ) VAR EndHour = IF ( EndMinute = 0, 1, 0 ) VAR EndTime = EventEnd - TIME ( EndHour, EndMinute, 0 ) VAR TimeTable = GENERATE ( SELECTCOLUMNS ( GENERATESERIES ( StartTime, EndTime, TIME ( 1, 0, 0 ) ), "Start Time", [Value] ), ROW ( "End Time", [Start Time] + TIME ( 1, 0, 0 ) ) ) RETURN TimeTable ), "DeviceID", [DeviceID], "Start Time", [Start Time], "End Time", [End Time], "Category", [Category], "Duration", VAR Difference = DATEDIFF ( MAX ( [Event Start], [Start Time] ), MIN ( [Event End], [End Time] ), MINUTE ) RETURN IF ( Difference > 0, Difference ) ), "% Duration", [Duration] / 60 )