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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
A_L
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:

DATEUSERBOOL
19/04/2020 19:30:00ALPHA0
19/04/2020 19:32:00ALPHA1
19/04/2020 19:32:30ALPHA1
20/04/2020 10:00:00ALPHA0
19/04/2020 19:32:00BRAVO1
19/04/2020 19:32:50BRAVO1
20/04/2020 11:00:00BRAVO0
19/04/2020 20:00:00CHARLIE0
19/04/2020 20:00:05DELTA0

 

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

Thanks!

1 ACCEPTED SOLUTION
camargos88
Community Champion
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!



View solution in original post

2 REPLIES 2
camargos88
Community Champion
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!



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

Kudos to you!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.