Forum Discussion
Timerange filter from selected row
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
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 ) )
2 Replies
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 ) )