Forum Discussion

brunborg's avatar
brunborg
Frequent Visitor
7 years ago
Solved

Filter on time interval

I am struggling with the problem of creating a filter / slicer that returns events that either starts, ends, or spans a time interval. Each event has a defined start time and end time, and I also have a Date table and a Month table.

 

Re: figure below - when the month of March is selected, events A, B, C and E should be returned, but D and F should not.

 

 

Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi brunborg ,

    Firstly, remove the relationship between Event table and data table, create relationship between data table and month table.

    The create a measure like below:

    Measure =
    IF (
        MIN ( Events[Start Date] ) IN VALUES ( 'Date'[Date] )
            || MIN ( Events[End Date] ) IN VALUES ( 'Date'[Date] )
            || (
                MIN ( Events[Start Date] ) < MIN ( 'Date'[Date] )
                    && MIN ( Events[End Date] ) > MAX ( 'Date'[Date] )
            ),
        1,
        0
    )

    Then drag this measure to FILTERS, set it like below:

    The result will like below:

    Best Regards,

    Teige

3 Replies

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    Hi brunborg ,

    Firstly, remove the relationship between Event table and data table, create relationship between data table and month table.

    The create a measure like below:

    Measure =
    IF (
        MIN ( Events[Start Date] ) IN VALUES ( 'Date'[Date] )
            || MIN ( Events[End Date] ) IN VALUES ( 'Date'[Date] )
            || (
                MIN ( Events[Start Date] ) < MIN ( 'Date'[Date] )
                    && MIN ( Events[End Date] ) > MAX ( 'Date'[Date] )
            ),
        1,
        0
    )

    Then drag this measure to FILTERS, set it like below:

    The result will like below:

    Best Regards,

    Teige

    • Juanan's avatar
      Juanan
      Regular Visitor
      7 years ago

      Hi,

       

      I've got the same data structure in various databases and this solution works but...

       

      It takes a looooong time to filter when aplying to not so large tables. I`ve got a table with no more than 5k records and when I try to filter the data the process starts but never ends with the result.

       

      Is there any other more efficient approach to get this done? I have to use the same technique with other databases that reach to more than 1M records and this solution doesn't seem to be useful in that case.

       

      Thank you!

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    brunborg add new measure as belo and then you can use this measure in visual level filter where value = 1

    Filter Based on Date = 
IF( 
    MIN( Table1[Start Date] ) IN VALUES( 'Calendar'[Date] )  || 
    MIN( Table1[End Date] ) IN VALUES ( 'Calendar'[Date] ) , 1 )