Add conditional column based on columns in two different tables without relationship

[Anonymous]

Hi,

I am new to Power BI and I am struggling with a task I am unable to figure out how to implement. I have two tables, both of which has an ID-field with the same type of ID. The first table has a date-column of when a transaction happened, and looks somewhat like this:
Table 1:

ID	Date (DD.MM.YYYY)	Other relevant stuff
1	1.1.2020	...
1	1.2.2020	...
2	1.2.2020	...
2	1.3.2020	...
2	1.4.2020	...
3	1.1.2020	...
3	1.2.2020	...

The second table has the ID-column, a columnd with a date marking the start of an interval, a column with a date marking the end of the interval, and an additional column with a product description:

Table 2:

ID	Start date	End date	Product
1	1.1.2020	1.2.2020	Bicycle
2	1.1.2020	1.2.2020	Bicycle
2	1.3.2020	1.5.2020	Cellphone
3	1.1.2020	1.2.2020	Chair

I would want the "Product"-column to be added to the first table, based on the ID and that the Date is between the Start date and End date. So I want the resulting table to be

ID	Date (DD.MM.YYYY)	Other relevant stuff	Product
1	1.1.2020	...	Bicycle
1	1.2.2020	...	Bicycle
2	1.2.2020	...	Bicycle
2	1.3.2020	...	Cellphone
2	1.4.2020	...	Cellphone
3	1.1.2020	...	Chair
3	1.2.2020	...	Chair

So for ID=2, we see that the product at Date=1.2.2020 should be "Bicycle", as Table1[ID] = Table2[ID] and Table1[Date] is between Table2[Start date] and Table2[End date].

Also note that both datasets are quite large (1.6 million rows in Table1 and 270k rows in Table2). Hoping someone are able to help!

[Jihwan_Kim]

Product CC =

VAR _currentID = Table1[ID]

VAR _resulttable =

FILTER (

Table2,

Table2[ID] = _currentID

&& Table2[Start date] <= Table1[Date (DD.MM.YYYY)]

&& Table2[End date] >= Table1[Date (DD.MM.YYYY)]

)

RETURN

DISTINCT ( SELECTCOLUMNS ( _resulttable, "@product", Table2[Product] ) )

Link to the pbix file 

[Anonymous]

Thank you so much!