Forum Discussion

Kumar11109's avatar
Kumar11109
Helper IV
9 years ago

Time Difference Between two resources

Hi All,    I am facing another problem with my data set. I am attaching an image for a sample of my dataset.    The data set contains the time spent by children in a session on different resource...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Kumar11109

 

Hi Kumar, no hurry. Please take your time. Hope you will get well soon.

 

Best Regards!

Dale

Kumar11109's avatar
Kumar11109
Helper IV
9 years ago

Hi v-jiascu-msft, 

 

I tried doing it on my data set. My data set is slightly big. There are about 2 mil rows. When I did it on my data set, it was taking too much time. I entered the formula and for two hours it just said "Working On It", but it never did :-/.   Is it because the data set is big or some other reason. My desktop has more than decent hardware ( 8 Gb ram, octa-core processor). But I did on a smaller data set it happened very quickly. 

 

Please help me. what might be the reason. Is there any other way can do the same and it happens very quickly. 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi Kumar11109,

     

    I had a test about 500k. It was very slow. I think the main cause is that we put a complicated formula on a large dataset. Let's split the formula into two parts as calculated columns. 

    NextStartTime =
CALCULATE (
    MIN ( Table1[Start Time] ),
    FILTER (
        Table1,
        Table1[Session ID] = EARLIER ( Table1[Session ID] )
            && Table1[Index]
                = EARLIER ( Table1[Index] ) + 1
    )
)
    TimeSpent =
IF ( ISBLANK ( [NextStartTime] ), BLANK (), [NextStartTime] - Table1[End Time] )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • Kumar11109's avatar
      Kumar11109
      Helper IV
      9 years ago

      HI v-jiascu-msft Dale, 

       

       I tried using your formula for that you provided me, but this time what happened was that it only calculated the next time for only 1 resource of a session and because of this it only calculated the idle time between for only those resources. I am attaching an image for the same. Also, the formula that you had shared, it was slightly different from the one you had shared earlier.  

       

       

      NextStartTime = CALCULATE ( MIN ( Table1[Start Time] ), FILTER ( Table1, Table1[Session ID] = EARLIER ( Table1[Session ID] ) && Table1[Index] = EARLIER ( Table1[Index] ) + 1 ) )

       

      TimeSpent = IF ( ISBLANK ( [NextStartTime] ), BLANK (), [NextStartTime] - Table1[End Time] )

       

       

       

       

      I tried doing it with the old formula, only separating the time spent and next time and didn't work again.

       

      NextStartTime = CALCULATE ( MIN ( Table1[Start Time] ), FILTER ( Table1, Table1[Index] >= EARLIER ( Table1[Index] ) + 1 && Table1[Session ID] = EARLIER ( Table1[Session ID] ) ))

       

      TimeSpent = IF ( ISBLANK ( NextStartTime ), BLANK (), NextStartTime - Table1[End Time] )

       

       

      Can we further break down formula? Also, can you explain me the formula? 

       

      Thank You, 

      Kumar Ashwarya

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        Kumar11109

         

        Hi Kumar,

         

        There is two preconditions that we evalute the time spent in one session and the data has orders. Have look at the picture. Let's take session 1 and 2 as example. Time Spent = [the next start time] - [the end time]. The "Time Spent" from session 1 to 2 will be empty. (row 5)

         

        In your latest post, you order the table by "resourceid", so it looks strange. Please reorder it as the orginal one (like below). It's clear there is three moves in session 1 and there will be three "time spent".

         

        I split one formula into two to get high performance. 

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards!

        Dale

         

         

         

  • Kumar11109's avatar
    Kumar11109
    Helper IV
    9 years ago

    One thing, my time format also contains the date. I hope this won't cause a problem???