Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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% |
Solved! Go to Solution.
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
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.
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
)
This is great, thank you. Simpler than I thought, I was over complicating it.
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
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |