Report elements of a column that occur in a limited time interval

[A_L]

Hello everyone! I'm quite new to Power BI and i have a task to solve.
Basically I have a table with Date column and User column, Date column is in dd/mm/yy hh:mm:ss format and User can appear different times during the days.
I would need to create a column with boolean values, let's say 1, if a User appears multiple times under a time interval (for instance under a minute).

I'll show a table as example:

DATE	USER	BOOL
19/04/2020 19:30:00	ALPHA	0
19/04/2020 19:32:00	ALPHA	1
19/04/2020 19:32:30	ALPHA	1
20/04/2020 10:00:00	ALPHA	0
19/04/2020 19:32:00	BRAVO	1
19/04/2020 19:32:50	BRAVO	1
20/04/2020 11:00:00	BRAVO	0
19/04/2020 20:00:00	CHARLIE	0
19/04/2020 20:00:05	DELTA	0

  I've tried several solutions found online but they do not match with my specific case.

Thanks!

[camargos88 · Accepted solution]

Hi @A_L ,

Try this calculated column:

Check =

VAR _date = 'Table'[DATE]

VAR _lastDate = CALCULATE(MAX('Table'[DATE]); FILTER('Table'; 'Table'[USER] = EARLIER('Table'[USER]) && 'Table'[DATE] < _date))

VAR _nextDate = CALCULATE(MIN('Table'[DATE]); FILTER('Table'; 'Table'[USER] = EARLIER('Table'[USER]) && 'Table'[DATE] > _date))

RETURN

IF(

(DATEDIFF(_lastDate; _date; MINUTE) <= 1 && ISBLANK(_lastDate) = FALSE()) ||

(DATEDIFF(_date; _nextDate; MINUTE) <= 1 && ISBLANK(_nextDate) = FALSE());

1;0)

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[A_L]

Thank you! It seems to work properly in my case with a more complex and huge data table.

Kudos to you!