Forum Discussion
Transform and summarize data from one table into another...
Ok, here is the problem I am faced with.
I have a data set that records everytime a piece of equipment is removed from service for being unserviceable, and records the time it went "DOWN" (removed) and the time it went back "UP" (returned to service).
What I want to be able to do is determine based on this data overall serviceability rates for all the equipment in a given period of time, and I can't seem to wrap my head around the problem to get what I need.
I know the total pieces of equipment available, but it might not be consistent on any given day. (New equipment added, or old equipment removed/obsoleted etc.)
The Up/Down records look like this essentially:
EQUIP_ID, EVENT_ID, DOWN_START (Date/Time field), DOWN_STOP (Date/Time field), DOWN_DURATION (minutes)
The DOWN_DURATION could sometimes span multiple days. Also, the quipment is expected to be UP more than it's DOWN, and it's certainly not down every day.
Basically, what I want to do is use this data to create a table like this:
DATE, EQUIP_ID, SERVICEABLE_TIME
where DATE is a given calendar date covering every date in a date range that could be filtered with a slicer, EQUIP_ID is the unique ID of the piece of equipment, and SERVICEABLE_TIME is the number of minutes the equipment was available and serviceable on that day.
So, I guess my issue here is where to even start with this. Me and DAX don't get along very well, so I am really up against a wall on this one.
My other thought, based on everyone's expertise here, is this even something that can be handled with DAX? Or should I move this transformation further back in my stack and do it serverside in Javascript before it gets sent out over the API to PowerBI?
Hi elietech .
The error was related with the calculation when you have a single day, I already fixed it by changing the formula to:
measure = VAR Date_Selection = MAX ( 'Dates'[Date] ) VAR Date_Selection_Next = Date_Selection + 1 VAR temp_table = FILTER ( ADDCOLUMNS ( UpDown; "Start"; DATE ( YEAR ( UpDown[DOWN_START] ); MONTH ( UpDown[DOWN_START] ); DAY ( UpDown[DOWN_START] ) ); "End"; DATE ( YEAR ( UpDown[DOWN_STOP] ); MONTH ( UpDown[DOWN_STOP] ); DAY ( UpDown[DOWN_STOP] ) ) ); [Start] <= Date_Selection && [End] >= Date_Selection ) VAR DateStart = IF ( MINX ( temp_table; UpDown[DOWN_START] ) <= Date_Selection; Date_Selection; MINX ( temp_table; UpDown[DOWN_START] ) ) VAR DateEnd = IF ( MAXX ( temp_table; UpDown[DOWN_STOP] ) >= Date_Selection_Next; Date_Selection_Next; MAXX ( temp_table; UpDown[DOWN_STOP] ) ) VAR Same_day_selection = IF ( MAXX ( temp_table; [Start] ) = MINX ( temp_table; [End] ) && MINX ( temp_table; UpDown[DOWN_START] ) >= Date_Selection; 1 ) RETURN IF ( DateEnd = BLANK (); 1440; IF ( Same_day_selection = BLANK (); DATEDIFF ( Date_Selection; DateStart; MINUTE ) + DATEDIFF ( DateEnd; Date_Selection_Next; MINUTE ); 1440 - CALCULATE ( SUM ( UpDown[DOWN_DURATION] ); FILTER ( temp_table; UpDown[DOWN_START] = DateStart ) ) ) )Check result below and attach.
10 Replies
Hi elietech ,
Looking at your information this is possible however you need to give some additional information on your Model.
I assume you have and equiment table that is related with your down. Create a calendar table disconnected for your filter and the add the following measure:
Measure = VAR Date_Selection = MAX ( 'calendar'[Date] ) VAR Date_Selection_Next = Date_Selection + 1 VAR temp_table = FILTER ( ADDCOLUMNS ( Up_Down; "Start"; DATE ( YEAR ( Up_Down[Down_start] ); MONTH ( Up_Down[Down_start] ); DAY ( Up_Down[Down_start] ) ); "End"; DATE ( YEAR ( Up_Down[Down_Stop] ); MONTH ( Up_Down[Down_Stop] ); DAY ( Up_Down[Down_Stop] ) ) ); [Start] <= Date_Selection && [End] >= Date_Selection ) VAR DateStart = IF ( MINX ( temp_table; Up_Down[Down_start] ) <= Date_Selection; Date_Selection; MINX ( temp_table; Up_Down[Down_start] ) ) VAR DateEnd = IF ( MAXX ( temp_table; Up_Down[Down_Stop] ) >= Date_Selection_Next; Date_Selection_Next; MAXX ( temp_table; Up_Down[Down_Stop] ) ) VAR Same_day_selection = IF ( MAXX ( temp_table; [Start] ) = MINX ( temp_table; [End] ) && MINX ( temp_table; Up_Down[Down_start] ) >= Date_Selection; 1 ) RETURN IF ( DateEnd = BLANK (); 1440; IF ( Same_day_selection = BLANK (); DATEDIFF ( Date_Selection; DateStart; MINUTE ) + DATEDIFF ( DateEnd; Date_Selection_Next; MINUTE ); 1440 - SUM ( Up_Down[Down_duration] ) * 1440 ) )Check final result in PBIX file attach.
Sorry for the delayed response...this looks very promising....one quick question before I start digging into it....my model already contains a "date/calendar" table...would I need to make another one? Or can I use the existing one? And yes, I do have an "Equipment" table that is relatable to the Up/Down events data....
Can't wait to give this a try...definately a level of DAX beyond what I am normally able to dream up on my own 🙂 I'm constantly amazed by the help and support I can find on this community.
