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
Kumar11109's avatar
Kumar11109
Helper IV
9 years ago
Hi Dale, I got sick so didn't have time to calculate this. Will use your idea and get back to you if I have any more questions.
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

    • 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???