Timerange filter from selected row

[Anonymous]

Hi everyone,

I have ran into problem and hope to get some nice tips here from the community. 🙂

I have two tables.

Table 1 basically defines different timeranges by the columns 'Start' and 'End':

ID	Start	End
1	1/1/2021	1/5/2021
2	1/4/2021	1/7/2021
3	1/6/2021	1/10/2021
4	1/9/2021	1/12/2021

Table 2 contains different datapoints with a column 'Date':

ID	Date	Value
1	1/1/2021	0.5
2	1/2/2021	5.2
3	1/3/2021	3.1
4	1/4/2021	4.9
5	1/5/2021	8.5
6	1/6/2021	2.5
7	1/7/2021	6.8
8	1/8/2021	0.5
9	1/9/2021	7.2

My goal is to filter Table 2 on 'Date' by the timerange defined by 'Start' and 'End' when selecting a row of Table 1.

What I have learned during my research is, that

1) I need a calculated measure instead of a calculated column to get 'Start' and 'End' values of the selected row of Table 1. 
2) I need a calculated column in Table 2 to check for every column if 'Date' is between 'Start' and 'End'

3) A calculated column can not access the updated value of a calculated measuere, because the measure calculation is executed after the calculation of a column and the calculation of a column gets not updated over time.

And this leads to the situation, that I am not able to filter Table 2 regarding the selected 'Start' and 'End' of Table 1.

One idea I had, to resolve that, is to do an Outer Join in beforehand on Table2.Date is between Table1.Start and Table.End. But this would leave we me with performance issues since my real data table contains almost a million events and thousands of timeranges.

I am verry happy to get any suggestion, ideas or hint how to deal with that kind of problem. 🙂

best regards and thanks a lot in advance,
Marvin

[Anonymous]

Hi Antonio, you are amazing! Thanks a lot! 🙂

[AntonioM · Accepted solution]

You can do all of those steps with a measure, which you use to filter Table 2. 

If you use:

Table Filter Measure = 
var 
    _start = SELECTEDVALUE('Table'[Start])
var 
    _end = SELECTEDVALUE('Table'[End])
var
    _date = SELECTEDVALUE('Table (2)'[Date])
return
    IF( _date <= _end && _date >= _start, 1,0)

 Then that will give you 1s for any row within the date range, when you select a row from Table

You can then apply that as a filter to a visual

Or to filter another measure, such as

Filtered Measure = CALCULATE ( [Measure], FILTER( Table2, [Table Filter Measure] = 1 ) )