Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

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

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!

1 ACCEPTED SOLUTION
Community Champion

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

2 REPLIES 2
Community Champion

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Regular Visitor

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

Kudos to you!

Helpful resources

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors