Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello everyone, on Power BI I'm trying to create a calculated column to detect if a Task is "Overlap" or "not". As in the image below for overlapping I mean when a Task Start Time and End Time overlap with another one like in the row 2 and 4. This overlap check should be done on each different User. So in the row 5 of the image below, that Task doesn't overlap with the row 4 because they are owned by two different users.
The Calculated Column that I created is the one below
OverlapOrNot =
VAR CurrentUser = 'Task'[User]
VAR CurrentStart = 'Task'[StartTime]
VAR CurrentEnd = 'Task'[EndTime]
VAR ConditionOverlap =
CALCULATE(
COUNTROWS('Task'),
FILTER(
'Task',
'Task'[User] = CurrentUser &&
(('Task'[StartTime] < CurrentEnd && 'Task'[EndTime]>CurrentStart ))))
RETURN
IF(ConditionOverlap>0,"Overlap","Not")
The problem with the formula above is that in the final result it gives "overlap" to each row on that column because when it start filtering, it compare the same Task with itself so of course will overlap.
My solution was to add this code line in the formula the variable for the "TaskName" and in the FILTER function to add the the Current TaskName is not equal to the one I want to compare.
OverlapOrNot =
VAR CurrentUser = 'Task'[User]
VAR CurrentStart = 'Task'[StartTime]
VAR CurrentEnd = 'Task'[EndTime]
VAR CurrentTask = 'Task'[TaskName]
VAR ConditionOverlap =
CALCULATE(
COUNTROWS('Task'),
FILTER(
'Task',
'Task'[User] = CurrentUser &&
'Task'[TaskName]<>CurrentTask &&
(('Task'[StartTime] < CurrentEnd && 'Task'[EndTime]>CurrentStart ))))
RETURN
IF(ConditionOverlap>0,"Overlap","Not")
Using this updated formula cause me the issue that when I try to Save it show me the message "Working on it" to the infinity without any update, so I don't know why just by adding to the FILTER function the code line to excude the same Task for comparison, the Column isn't able to be saved.
If someone can help me out to find a solution would be much appreciate it.
Thank in advance
Solved! Go to Solution.
Maybe try it without the CALCULATE. I don't think you want that context transition.
OverlapOrNot =
VAR CurrentUser = 'Task'[User]
VAR CurrentStart = 'Task'[StartTime]
VAR CurrentEnd = 'Task'[EndTime]
VAR CurrentTask = 'Task'[TaskName]
VAR ConditionOverlap =
COUNTROWS (
FILTER (
'Task',
'Task'[User] = CurrentUser
&& 'Task'[TaskName] <> CurrentTask
&& ( ( 'Task'[StartTime] < CurrentEnd )
&& ( 'Task'[EndTime] > CurrentStart ) )
)
)
RETURN
IF (
ConditionOverlap > 0,
"Overlap",
"Not"
)
Maybe try it without the CALCULATE. I don't think you want that context transition.
OverlapOrNot =
VAR CurrentUser = 'Task'[User]
VAR CurrentStart = 'Task'[StartTime]
VAR CurrentEnd = 'Task'[EndTime]
VAR CurrentTask = 'Task'[TaskName]
VAR ConditionOverlap =
COUNTROWS (
FILTER (
'Task',
'Task'[User] = CurrentUser
&& 'Task'[TaskName] <> CurrentTask
&& ( ( 'Task'[StartTime] < CurrentEnd )
&& ( 'Task'[EndTime] > CurrentStart ) )
)
)
RETURN
IF (
ConditionOverlap > 0,
"Overlap",
"Not"
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
13 | |
12 | |
11 | |
11 | |
8 |