Forum Discussion
Helper IVConnections between tables using date intervals
Hi,
I have a dataset in which i need to connect two tables using date fields. However, these are intervals, such as "START_DATE" and "END_DATE". A simplified example would be:
table 1:
| start_date | end_date | value |
| 1/1/2022 | 1/6/2022 | A |
| 31/6/2022 | 31/12/2022 | B |
table 2:
| start_date | end_date | correspondent_value |
| 1/3/2022 | 1/4/2022 | X |
| 31/9/2022 | 30/11/2022 | Y |
Choosing a slicer with the A value would result in the corresponding value being X. How can i connect these two tables?
Thanks.
Hi qmestu ,
Please try:
Apply the measure to the visual level's filter:
Measure = IF(SELECTEDVALUE('Table 2'[start_date])>=MIN('Table 1'[start_date])&&SELECTEDVALUE('Table 2'[end_date])<=MAX('Table 1'[end_date]),1)Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- lbendlin
Super User
There be dragons. What should happen when multiple intervals from Table 2 match your selection from table 1? What if the use selects multiple entries from table 1?
Please consider all possible scenarios and then provide sample data that fully covers them all.
v-jianboli-msftCommunity Support
Hi qmestu ,
Please try:
Apply the measure to the visual level's filter:
Measure = IF(SELECTEDVALUE('Table 2'[start_date])>=MIN('Table 1'[start_date])&&SELECTEDVALUE('Table 2'[end_date])<=MAX('Table 1'[end_date]),1)Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
