Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

detecting overlapping activities (all occurences)

Hi.   1) I would like to use a DAX formula that can detect a time overlap for the same person, being in two places at once (all occurrences).   2) Moreover, I would also like the formula to detec...
  • M_L's avatar
    M_L
    6 years ago

    The 3rd condition "__condition_start_equal" could be added like below. 

    Please indicate if this is this fine.

     

    Question 1 Period Overlap = 
VAR _Person = 'Table'[Person] 
VAR _start = 'Table'[Start_date_full] 
VAR _end = 'Table'[End_Date_full]
VAR _condition_start_before= CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start < 'Table'[Start_date_full] && _end >'Table'[Start_date_full])) 
VAR _condition_start_between = CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start > 'Table'[Start_date_full] && _start < 'Table'[End_Date_full])) 
VAR _condition_start_equal = CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start = 'Table'[Start_date_full] )) 
VAR __results = If(_condition_start_between + _condition_start_before + _condition_start_equal <= 1 ;"no";"yes")
RETURN 
__results

     

    this one is shorter : 

    Question 1 Period Overlap short = 
VAR _Person = 'Table'[Person] 
VAR _start = 'Table'[Start_date_full] 
VAR _end = 'Table'[End_Date_full]

VAR _condition_overlap = CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person 
&& ((_start < 'Table'[Start_date_full] && _end >'Table'[Start_date_full]) 
|| _start = 'Table'[Start_date_full] 
|| (_start > 'Table'[Start_date_full] && _start < 'Table'[End_Date_full]))))

RETURN If(_condition_overlap <= 1 ;"no";"yes")

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

new Gantt chart

M_L's avatar
M_L
Frequent Visitor
6 years ago

The 3rd condition "__condition_start_equal" could be added like below. 

Please indicate if this is this fine.

 

Question 1 Period Overlap = 
VAR _Person = 'Table'[Person] 
VAR _start = 'Table'[Start_date_full] 
VAR _end = 'Table'[End_Date_full]
VAR _condition_start_before= CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start < 'Table'[Start_date_full] && _end >'Table'[Start_date_full])) 
VAR _condition_start_between = CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start > 'Table'[Start_date_full] && _start < 'Table'[End_Date_full])) 
VAR _condition_start_equal = CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start = 'Table'[Start_date_full] )) 
VAR __results = If(_condition_start_between + _condition_start_before + _condition_start_equal <= 1 ;"no";"yes")
RETURN 
__results

 

this one is shorter : 

Question 1 Period Overlap short = 
VAR _Person = 'Table'[Person] 
VAR _start = 'Table'[Start_date_full] 
VAR _end = 'Table'[End_Date_full]

VAR _condition_overlap = CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person 
&& ((_start < 'Table'[Start_date_full] && _end >'Table'[Start_date_full]) 
|| _start = 'Table'[Start_date_full] 
|| (_start > 'Table'[Start_date_full] && _start < 'Table'[End_Date_full]))))

RETURN If(_condition_overlap <= 1 ;"no";"yes")

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Dear M_L,

Your DAX statement is now perfect for my purposes.

Thank you so much for your help,
@masG

     

  • alessiobayware's avatar
    alessiobayware
    Regular Visitor
    2 years ago

    Hi, this DAX looks pretty good but by trying to using it, the issue is that it compare with the first row with itself. I tried to add to the filter to exclude the same row that it's trying to compare but after saving it keeps showing "working on it" to the infinity