Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Overlap

OK, I tried all different kinds of solutions to this and finally decided to just nuke it from orbit. Just to be sure... So basically, the task was that given a set of overlapping meetings, find the total amount of time spent in meetings. So, the basic process is to blow out the meetings from the start to the end for every minute of the day and then check if each minute is actually included in a meeting or not. That's the gist of it. 

 

Overlap = 
    VAR __Start = MIN('Table'[Start])
    VAR __End = MAX('Table'[End])
RETURN
    IF(ISBLANK(MAX([Start])),BLANK(),
        VAR __Table = GENERATESERIES(__Start,__End,1/24/60)
        VAR __Table1 = ALL('Table')
        VAR __Table2 = GENERATE(__Table,__Table1)
        VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF([Value]>=[Start] && [Value] <= [End],1,0))
        VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",MAXX(CURRENTGROUP(),[Include]))
    RETURN
        SUMX(__Table4,[Minute])/60
    )

I also threw in a "Not Included" version that kind of does the opposite:

Not Included = 
    VAR __Start = MIN('Table'[Start])
    VAR __End = MAX('Table'[End])
RETURN
    IF(ISBLANK(MAX([Start])),BLANK(),
        VAR __Table = GENERATESERIES(__Start,__End,1/24/60)
        VAR __Table1 = ALL('Table')
        VAR __Table2 = GENERATE(__Table,__Table1)
        VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF(COUNTROWS(FILTER(__Table1,[Value]>=[Start] && [Value] <= [End]))>=1,0,1))
        VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",MAXX(CURRENTGROUP(),[Include]))
    RETURN
        SUMX(__Table4,[Minute])/60
    )

 

 

2 Replies

  • Euto's avatar
    Euto
    Frequent Visitor

    Greg_Deckler  Wow, this for sure a variation of a problem I am trying to solve. 

    I have some observation and questions based on this, that may help me solve my problem.

     

    Here are my Observations

     - I see that when I select a particular meeting, the Overlap Card visual give me either 1.00 or 0.50 which is good. And by clicking each indivual meeting I was able to get the following result for each index

    1 - 1.00
    2 - 0.05
    3 - 0.05
    4 - 0.05
    5 - 1.00
    6 - 1.00
    7 - 1.00
    8 - 1.00

     

    - I also noticed that when no meeting is selected, the Overlap Card returns 3.00

     

    Here are my Questions

    1. How do I get the Overlap Card to return the Sum of the Overlap result (5.15) when their's no filter?

     

    2. How do I get the result to sum up correctectly when a few meetings a selected?

    Thanks in advance.