cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Re: Time Difference Between two resources

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

 

Highlighted
Microsoft
Microsoft

Re: Time Difference Between two resources

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] )

Time Difference Between two resources.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper IV
Helper IV

Re: Time Difference Between two resources

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] )

Screenshot (4).png

 

 

 

 

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

Highlighted
Microsoft
Microsoft

Re: Time Difference Between two resources

@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. 

Time Difference Between two resources 2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper IV
Helper IV

Re: Time Difference Between two resources

I did not sort it this time, but its still working the same way. 

 

Look at the formula that I have applied, I hope it's not incorrect. 

 

Screenshot (6).png

 

 

Highlighted
Microsoft
Microsoft

Re: Time Difference Between two resources

@Kumar11109

 

Hi Kumar, 

 

You can see the index is disordered. That's why the result is wrong.  If we don't use an index, the performance will be terrible due to we have to filter and compare. Let's add a useful index.

1. Open "Query Editor", select "Session ID", sort a->z;

2. Click to open "Advanced Editor", add ", {"Start Time", Order.Ascending}" as the picture showed;

3. Click "Done", we will see an arrow in "session ID" and an arrow in "Start Time";

4. Add index now.

Why we do this? There is an order along time when people move from one resource to another in one session. If this is true, it would work. We can use (index + 1) to get the next "Start Time" without comparing many "Start Time" to get the most recent "Start Time", which is consuming performance.Time Difference Between two resources 3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors
Users online (1,145)