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,

 

Is your problem solved? What can I do for you?

 

Best Regards!

Dale

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