Count time entries as 1 one if is less than 25 minutes

[Raveen]

Hello, i am struggling with getting unique count if centains rows falls winthin 25 min.

Request is to count unique actions based on multiple columns.

If Agent, Company, Type is the same and Time is less than 25 minutes from first entry it should not count.

Agent	Company	Type	Date + time	Date	Time	Count as action
John	Restaurant AAA	Incident	09/11/2020 09:23	09/11/2020	09:23:00	1
John	Restaurant AAA	Incident	09/11/2020 09:23	09/11/2020	09:23:00
John	Restaurant AAA	Incident	09/11/2020 09:24	09/11/2020	09:24:00
John	Restaurant AAA	Incident	09/11/2020 09:24	09/11/2020	09:24:00
Karen	Hotel AAA	Incident	09/11/2020 09:25	09/11/2020	09:25:00	1
Karen	Hotel AAA	Incident	09/11/2020 09:25	09/11/2020	09:25:00
John	Café AAA	Incident	09/11/2020 09:25	09/11/2020	09:25:00	1
John	Café AAA	Incident	09/11/2020 09:26	09/11/2020	09:26:00
John	Café AAA	Incident	09/11/2020 09:26	09/11/2020	09:26:00
John	Café AAA	Incident	09/11/2020 09:28	09/11/2020	09:28:00
John	Café AAA	Issue	09/11/2020 09:28	09/11/2020	09:28:00	1
John	Restaurant BBB	Incident	09/11/2020 09:29	09/11/2020	09:29:00	1
John	Restaurant BBB	Incident	09/11/2020 09:30	09/11/2020	09:30:00
John	Restaurant BBB	Incident	09/11/2020 09:32	09/11/2020	09:32:00
John	Restaurant BBB	Incident	09/11/2020 09:33	09/11/2020	09:33:00
John	Restaurant BBB	Issue	09/11/2020 09:34	09/11/2020	09:34:00	1
Karen	Hotel BBB	Incident	09/11/2020 09:34	09/11/2020	09:34:00	1
Karen	Hotel BBB	Incident	09/11/2020 09:35	09/11/2020	09:35:00
Karen	Hotel BBB	Incident	09/11/2020 09:36	09/11/2020	09:36:00
Karen	Hotel BBB	Incident	09/11/2020 09:36	09/11/2020	09:36:00
John	Café AAA	Incident	09/11/2020 09:40	09/11/2020	09:40:00
John	Café AAA	Incident	09/11/2020 09:40	09/11/2020	09:40:00
John	Café AAA	Incident	09/11/2020 09:40	09/11/2020	09:40:00
John	Café AAA	Incident	09/11/2020 09:41	09/11/2020	09:41:00
John	Café AAA	Incident	09/11/2020 09:41	09/11/2020	09:41:00
John	Café AAA	Incident	09/11/2020 09:41	09/11/2020	09:41:00
John	Café AAA	Incident	09/11/2020 09:51	09/11/2020	09:51:00	1

I am not sure how to start with the time column, if i could be pointed in the right direction please.

I did find some posts about time entries or time tables but i could not get them to work for my case.

[v-yalanwu-msft]

Hi @Raveen  , 

You could create three measure as follows:

1.First Request ( create a measure to count unique actions)

count as action =
VAR _diff =
    DATEDIFF (
        CALCULATE (
            MIN ( [Date + time] ),
            ALLEXCEPT ( 'Table', 'Table'[Agent], 'Table'[Company], 'Table'[Type] )),
        MAX ( [Date + time] ),
        MINUTE)
VAR _indexmin =
    CALCULATE (
        MIN ( [Index] ),
        ALLEXCEPT ( 'Table', 'Table'[Agent], 'Table'[Type], 'Table'[Company] ))
RETURN
    IF ( _diff = 0 && MAX ( [Index] ) = _indexmin, 1 )

2.Second Request ( create a measure to sum count)

Measue1:

Flag =
VAR _diff =
    DATEDIFF (
        CALCULATE (
            MIN ( [Date + time] ),
            ALLEXCEPT ( 'Table', 'Table'[Agent], 'Table'[Company] )
        ),
        MAX ( [Date + time] ),
        MINUTE
    )
VAR _indexmin =
    CALCULATE (
        MIN ( [Index] ),
        ALLEXCEPT ( 'Table', 'Table'[Agent], 'Table'[Company] )
    )
RETURN
    IF ( _diff = 0 && MAX ( [Index] ) = _indexmin, 1 )

 Measue2:

sum =SUMX ( FILTER ( ALL ( 'Table' ), [Index] <= MAX ( [Index] ) ), [Flag] )

Measue3:

# Count =
MINX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Agent] = MAX ( 'Table'[Agent] )
            && 'Table'[Company] = MAX ( 'Table'[Company] )
            && 'Table'[Type] = MAX ( 'Table'[Type] )),
    [sum])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Raveen]

@v-yalanwu-msft 

This is almost what i need, just missing the "Count row if more than 25 minutes difference".

If you would add a new row(28) in the table:

This one should be counted as new action.

If there are more rows with the same Agent, Company, Type and the time is 10:11:00 or more it should count as another action and so on for that day.

regards, raveen

[v-yalanwu-msft · Accepted solution]

Hi @Raveen  ，

You could modify “count as action” measure as follows:

count as action =
VAR _diff =
    DATEDIFF (
        CALCULATE (
            MIN ( [Date + time] ),
            ALLEXCEPT ( 'Table', 'Table'[Agent], 'Table'[Company], 'Table'[Type] )
        ),
        MAX ( [Date + time] ),
        MINUTE
    )
VAR _indexmin =
    CALCULATE (
        MIN ( [Index] ),
        ALLEXCEPT ( 'Table', 'Table'[Agent], 'Table'[Type], 'Table'[Company] )
    )
RETURN
    IF ( _diff > 25, 1, IF ( MAX ( [Index] ) = _indexmin, 1 ) )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[Raveen]

@v-yalanwu-msft 

Need some time to check if it produces the correct result, wil update soon.

As a follow up, would it be possible to add the same columns in the data table?

[v-yalanwu-msft]

Hi, @Raveen ,

You can see my pbix file. I have changed the correct answer, but I don't know whether it matches your logic. If it's not the result you want, can you provide the picture of the result you want, or more details and logic.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Fowmy]

@Raveen 

Need a bit more clarity on your explanation. Can you explain how you calculated values as one in "Count as Action" with examples? 

[Raveen]

Sorry for the picture, keep getting errors when posting.

[Raveen]

Of course, every combination of colum A, B, C and G is counted as action.

Then if the colum G(Time) is more then 25 with the same combination.

when to count as action:

Action 1. Row 8 is the first action with "John"(A), "Cafe; AAA"(B) , "Incident"(C) and "09:25:00"(G).

Row 22 - 28 have the same combination as row 8 and the G(Time) is in the range of "09:25:00" + 25 minutes = 09:50:00, they are not counted.

Action 2. Row 12 colum C has different value "Issue" thus counted as a seperate action.

Action 3. Row 29 have the same combination as row 8 but the time"09:51:00"(G) is not in the range of "09:25:00" + 25 minutes = 09:50:00.

Does this help @Fowmy ?

Thank you for having a look.

[Fowmy]

@Raveen 

In your sample data, I don't see a column that is unique between entries, for example,  what is the difference between rows 2 and 3? How do I distinguish bettwen them?

[Raveen]

Data comes from a system where the user gets notification to perform a manual action.

Depending on the action type there could be one or multiple follow up actions to do.

Every action comes out as a seperate row in the daily data exports.

The requirement is when the user takes more thaan 25 minutes to handle the next action it schould be counted as a seperate action.

Of course this is only for the same Agent, Company and Type on that day.

@Fowmy 

[Raveen]

@Fowmy 

There are additional columns like, Action type, sub-type and result for each row.

[Fowmy]

@Raveen 

Okay, I suggest you share a sample that exactly represent your actual data.