Relate two tables by a date range.- DirectQuery

[Jug]

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!

[Anonymous · Accepted solution]

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.           

[amitchandak]

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