Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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"
)
User | Count |
---|---|
12 | |
12 | |
8 | |
8 | |
6 |
User | Count |
---|---|
27 | |
19 | |
13 | |
11 | |
7 |