Group by with timestamp in 3 minutes

[Jeffrey_VC]

Hi everyone,

I'm trying to make a Dax code that gives me a count of events but if there in the timestamp between them is less than 2 minutes that this counts as 1 event.

For example:

Event	date	time
A	1-1-2024	8:00:00
A	1-1-2024	8:00:42
B	1-1-2024	8:01:32
A	1-1-2024	8:01:58
B	1-1-2024	8:02:00
A	1-1-2024	8:07:00
B	1-1-2024	8:08:52
A	1-1-2024	8:21:00
B	1-1-2024	8:35:00
B	1-1-2024	8:35:58

Needed results:

Event A: 3

Event B; 3

Thnx for the support.

[SachinNandanwar · Accepted solution]

I assume you mean that if the diffrence between the two consecutive times for an event is less than 2 minutes then it should be considered as single record

For example difference between 00:00 and 00:42 is less than 2 minutes and so is the difference between 00:42 and 01:58 less than 2 minutes , so both should be considered as a one count.difference between 01:58 and 07:00 is greater than 2 so it should be considered one new count.

Create a measure. t is the table name

TimeDifference =
VAR _M =
    DATEDIFF (
        CALCULATE (
            MAX ( t[time] ),
            OFFSET (
                -1,
                ( ALL ( t ) ),
                ORDERBY ( t[date].[Date], DESC ),
                ,
                PARTITIONBY ( t[Event] ),
                MATCHBY ( t[date].[Date], t[time] )
            )
        ),
        MAX( t[time] ),
        MINUTE
    )
RETURN
    IF ( _M < 2, 0, _M )

Then create a table

Table = SUMMARIZE(t,t[Event],t[date],t[time],"TimeDifference",[TimeDifference])

and create a new measure in the table to get the distinct count

FinalCount = DISTINCTCOUNT('Table'[TimeDifference])

[Jeffrey_VC]

Hello @SachinNandanwar 

Thank you for your answer.

I have tried to use your dax, but I get an error:

[SachinNandanwar]

What is the error that you get ?

[Jeffrey_VC]

@SachinNandanwar

In the dax i get an error on:

ORDERBY ( 'Logdata SQL'[Datum].[datum],DESC ), and

MATCHBY ( 'Logdata SQL'[Datum].[datum], 'Logdata SQL'[Tijd])

[SachinNandanwar]

I want to know the error message. After you create the measure it should give you an error message.

[Jeffrey_VC]

@SachinNandanwar 

The column reference to Date in the SQL Logdata table cannot be used for the date variant because it does not contain a variant.

[SachinNandanwar]

I suspect it has to do with the data type of the column. Can you share the PBI file with some mock data ?

[Jeffrey_VC]

How can i share a PBI?

[SachinNandanwar]

put it on any file sharing app.

[Jeffrey_VC]

https://drive.google.com/file/d/1i4N-_aivicwfQV-fo-rT3-Yt_JXZFJzi/view?usp=sharing

[SachinNandanwar]

Also check this post.It has some great insights.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Column-reference-to-Date-in-table-ca...

[ThxAlot]

Easy enough,

[ValtteriN]

Hi,

Here is one way to do this with dax:

Count of events =

VAR _vtable1 =

        ADDCOLUMNS(

        'Table (43)',

        "DateTime", 'Table (43)'[date] + 'Table (43)'[time],

        "Earlier",

        var _time = [time]

        var _date = [date]

        var _event = [Event]

        RETURN

        CALCULATE(

            MAX('Table (43)'[date]) + MAX('Table (43)'[time]),

            FILTER(

                'Table (43)',

                'Table (43)'[Event] = _event &&

                ('Table (43)'[date] + 'Table (43)'[time]) < (_date + _time)

            )

        )

    )

VAR _vtable2 =

    ADDCOLUMNS(

        _vtable1,

        "lessthan2",

        SWITCH(

            TRUE(),

            ISBLANK([Earlier]), 1,  -- If there is no earlier event, treat it as a new event

            DATEDIFF([Earlier], [DateTime], MINUTE) < 2, 0,  -- If less than 2 minutes, treat it as the same event

            1  -- Otherwise, treat it as a new event

        )

    )

RETURN

SUMX(_vtable2, [lessthan2])

You can visualize this in dax query view:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/