Split one raw in several rows based on a condition

[Nandor]

Hi,

I need oprepare charts to evaluate the up time and down time of the machine.

The operator has a tablet and logs the type of activity.

The data looks like this:

Activity_Type_All	Activity_Start_Date&Time	Activity_End_Date&Time	Activity_Duration_HoursDecimalFormat
Productive	05/09/2016 06:29	05/09/2016 06:30	0.02
Unproductive	05/09/2016 06:30	05/09/2016 09:00	2.5
Productive	05/09/2016 09:00	05/09/2016 16:43	7.72
Unproductive	05/09/2016 16:43	05/09/2016 17:51	1.13
Productive	05/09/2016 17:51	05/09/2016 17:59	0.13
Productive	05/09/2016 17:59	05/09/2016 21:48	3.82
Unproductive	05/09/2016 21:48	05/09/2016 21:55	0.12
Unproductive	05/09/2016 21:55	06/09/2016 06:32	8.62

The shifts are as follows:

	Hour	Minute
Shift 1	6	30
Shift 2	14	30
Shift 3	22	30

To be able to make charts that evaluate Shifts and days I would need to split the activity when going from one shift to the other or when it passes midnight.

 So I would need to repalce row 3:

Productive

05/09/2016 09:00

05/09/2016 16:43

7.72

with the following two rows:

Productive	05/09/2016 09:00	05/09/2016 14:30	5.5
Productive	05/09/2016 14:30	05/09/2016 16:43	2.22

The activity that is happening at midnight, should also split.

Thank you,

Nandor

[v-haibl-msft · Accepted solution]

@Nandor

There is no function can split one row to several rows with DAX. The only method I can think out is first create several calculated columns for different time period, then create several calculated tables and union them at last. Please refer to following screenshot as below.

I’ve also upload my .pbix file here for reference. This method is complex and you can take a look at it.

Best Regards,

Herbert

[Nandor]

Thank you, I will explore this aproach.

Best regards,

Nandor

[v-haibl-msft]

@Nandor

How about the result if we can categorize each row to “shift1”, “shift1-2”, “shift2”, “shift2-3”, “shift3” and “shift1-3”?

BTW, what do the values in horizontal axis (36.1, 36.2, 37.5) mean?

Best Regards,

Herbert

[Nandor]

It will not help to make mixed shifts because for each shift we a have a head of team who is has bonuses if she reaches a certin procentage of up time.

The x axis is the week number and the day ( 36 1 is week 36, 1st of september).

[ankitpatira]

@Nandor I see your problem. Wouldn't it be easier instead of splitting rows in activity table by shifts you simply create a calculated column in activity table that gives you shift that activity fall under. So in activity table create calculated column as,

ActivityEndHourMin = HOUR(sam[Activity_End_Date&Time]) & MINUTE(sam[Activity_End_Date&Time])

Change type of column to Whole Number.

In shift table create calculated column as,

HouMin = sam1[Hour] & sam1[Minute]

Change type to Whole Number.

Then finallly in activity table create another calculated column as,

Column = CALCULATE( FIRSTNONBLANK(SHIFTTABLE[shift ], TRUE), FILTER(SHIFTTABLE, ACTIVITYTABLE[ActivityEndHourMin] <= SHIFTTABLE[HouMin] ))

[Nandor]

Dear Ankit,

The main problem is that I need to work with the activity duration, the last column of my activity table, the number of hours in decimal format.

For example because we don't work in shift 3 and in the weekends, the activity named "No work Hours" has 56 hours, because it starts on Friday at 10.30PM and ends on Monday 6.30 AM.  All 56 hours go to Friday.

Another example is when one shift "steals" an activity from the other shift. Please follow the second bar on the below chart, where Shift 1 steals the activity from Shift 2:

No shift is selected:

 

If I select shift 2:

This happends because the "Production" activity started in shift one, and when I select shift 2 it does not show that activity because the start time is NOT in shift 2. What we see in shift 2 is 100% unproductive activity because it represents only 40 minutes of "Celaning" activity, that happend at the end of the shift, the productive activity went to shift 1.

Thank you,

Nandor