Adding sessions which are nearby

[Kumar11109]

Dear All, 

I am facing a problem. I want to consider the session of the same group which has started nearby. 

For ex. session 1 and 2 have started one after the other for the group 3. I want to consider sessions which have started in a difference of 5 mins for the same group as one and would want to rename them as one. 

I have attached the image of a sample data set. 

How can do this?

Would appreciate the help. 

Thank You, 

[v-sihou-msft]

@Kumar11109

I get it. You don't need to take the Resource Level scenario. 

I tried sample data like below (a little bit different than yours):

Firstly, you need to add two calculated column to get the MinStartTime and MaxEndTime for current Session within current Group.

MinStartTime = CALCULATE(MIN('Session'[StartTime]),ALLEXCEPT('Session','Session'[SessionID],'Session'[GroupID]))

MaxEndTime = CALCULATE(MAX('Session'[EndTime]),ALLEXCEPT('Session','Session'[SessionID],'Session'[GroupID]))

Then create a MinutesDiff column to calculate the difference between current MinStartTime and previous session MaxEndTime.

MinutesDiff = 
1
    * (
         'Session'[MinStartTime] 
            - CALCULATE (
                MAX ( 'Session'[MaxEndTime] ),
                FILTER (
                   'Session',
                    'Session'[SessionID]
                        = EARLIER ( 'Session'[SessionID] ) - 1 && 'Session'[GroupID]=EARLIER('Session'[GroupID])
                        
                )
            )
    )
    * 1440

Now you need to re-tag the Sessions to make "nearby" session as same one. Before that, we need a "Flag" column based on above MinutesDiff column. 

Flag = IF('Session'[MinutesDiff] > 5,1,0)

Then calculate the running total of above column to make all less that 5 minutes difference sessions into same group. 

SessionGroup = CALCULATE(SUM('Session'[Flag]),FILTER('Session','Session'[SessionID]<=EARLIER('Session'[SessionID])))

The result looks like below:

Regards,

[Kumar11109]

@v-sihou-msft I tried using the formula. But when we are finding out the difference, I was not able to calculate it because my groups id and session id's are alphanumeric and so the earlier command is not able to run. I am sending you screenshot. I tried finding a solution, but to no avail. Can you please help me. 

[Kumar11109]

@v-sihou-msft

I tried using the formula that you had given, but because my session and group id are alphanumeric, I wasn't able to compare the numbers. 

Can you please help me. 

Thank You, 

Kumar Ashwarya 

[v-sihou-msft]

@Kumar11109

I'm still not clear about your requirement. Do you need to take the Resource level in your logic? For example, the first Resource for Session 2 is started on 8:57:23. This time should be compared with the first Resource under Session 1, or the last one?

[Kumar11109]

@v-sihou-msft would you be able to guide me to the command which might help me in doing this? 

[Kumar11109]

I think i should it put as if, because the session 1 and 2 are in difference of only 1 minutes, I should be able to consider those 2 sessions as 1 for group with group id 3. Similarly for session 3 and 4 also should be 1 for group with id 5.