Forum Discussion
Relate two tables by a date range.- DirectQuery
I need to relate two tables by a code and a date range. For example:
where TABLE1.SERVICE_CODE = TABLE2.SERVICE_CODE and TABLE1.SERVICE_DATE between TABLE2.START_DATE and TABEL2.END_DATE
Table 1 is the fact table that has sales information and has the service code and date of execution of the service. I need to relate to table 2 to bring the description of the service, the relationship must be made through the code and the date range. Table 2 has two date fields, start date and end date, because the same code can reference two different descriptions of the services depending on the date range. The date in table 1 must be between the date range of table 2.
Is there any way to relate the tables like this or adjust or add to the import code? I'm using directquery
Thanks!
Hi Jug ,
You can create a measure for filtering.
Measure = IF(MAX('TABLE 1'[SERVICE_CODE])=MAX('TABLE 2'[SERVICE_CODE])&&MAX('TABLE 1'[SERVICE_DATE])>=MAX('TABLE 2'[START_DATE])&&MAX('TABLE 1'[SERVICE_DATE])<=MAX('TABLE 2'[END_DATE]),1)No relationship between tables.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Jug , a new measure
maxx(filter(Table2, max(Table1[Service_code]) = Table2[Service_code] && max( Table1[service_date] ) >= (Table2[Start_date]) && max(Table1[service_date]) <= max(Table2[Start_date] ) ), Table2[Service Description])
Hi Jug ,
You can create a measure for filtering.
Measure = IF(MAX('TABLE 1'[SERVICE_CODE])=MAX('TABLE 2'[SERVICE_CODE])&&MAX('TABLE 1'[SERVICE_DATE])>=MAX('TABLE 2'[START_DATE])&&MAX('TABLE 1'[SERVICE_DATE])<=MAX('TABLE 2'[END_DATE]),1)No relationship between tables.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
