Filter on time interval

[brunborg]

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.

[TeigeGao · Accepted solution]

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]

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]

@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 )