Forum Discussion

Nandor's avatar
Nandor
Icon for Helper I rankHelper I
10 years ago
Solved

Split one raw in several rows based on a condition

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_Al...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    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

ankitpatira's avatar
ankitpatira
Icon for Community Champion rankCommunity Champion
10 years ago

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's avatar
    Nandor
    Icon for Helper I rankHelper I
    10 years ago

    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