topic Re: Group by with timestamp in 3 minutes in DAX Commands and Tips

Group by with timestamp in 3 minutes

Jeffrey_VC — Thu, 10 Oct 2024 12:51:50 GMT

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.

Re: Group by with timestamp in 3 minutes

ValtteriN — Thu, 10 Oct 2024 19:48:55 GMT

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/

Re: Group by with timestamp in 3 minutes

ThxAlot — Thu, 10 Oct 2024 21:51:27 GMT

Easy enough,

Re: Group by with timestamp in 3 minutes

SachinNandanwar — Fri, 11 Oct 2024 00:09:21 GMT

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

Re: Group by with timestamp in 3 minutes

Jeffrey_VC — Fri, 11 Oct 2024 07:01:50 GMT

Hello SachinNandanwar

Thank you for your answer.

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

Re: Group by with timestamp in 3 minutes

SachinNandanwar — Fri, 11 Oct 2024 12:10:53 GMT

What is the error that you get ?

Re: Group by with timestamp in 3 minutes

Jeffrey_VC — Fri, 11 Oct 2024 12:54:53 GMT

SachinNandanwar

In the dax i get an error on:

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

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

Re: Group by with timestamp in 3 minutes

SachinNandanwar — Fri, 11 Oct 2024 13:28:14 GMT

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

Re: Group by with timestamp in 3 minutes

Jeffrey_VC — Fri, 11 Oct 2024 13:37:54 GMT

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.

Re: Group by with timestamp in 3 minutes

SachinNandanwar — Fri, 11 Oct 2024 13:42:33 GMT

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

Re: Group by with timestamp in 3 minutes

Jeffrey_VC — Fri, 11 Oct 2024 13:51:11 GMT

How can i share a PBI?

Re: Group by with timestamp in 3 minutes

SachinNandanwar — Fri, 11 Oct 2024 14:04:23 GMT

put it on any file sharing app.

Re: Group by with timestamp in 3 minutes

Jeffrey_VC — Fri, 11 Oct 2024 14:16:08 GMT

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

Re: Group by with timestamp in 3 minutes

SachinNandanwar — Fri, 11 Oct 2024 14:32:59 GMT

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-cannot-be-used-with-a/m-p/996750