Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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:

IDDate (DD.MM.YYYY)Other relevant stuff
11.1.2020...
11.2.2020...
21.2.2020...
21.3.2020...
21.4.2020...
31.1.2020...
31.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:

IDStart dateEnd dateProduct
11.1.20201.2.2020Bicycle
21.1.20201.2.2020Bicycle
21.3.20201.5.2020Cellphone
31.1.20201.2.2020Chair

 

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

 

IDDate (DD.MM.YYYY)Other relevant stuffProduct
11.1.2020...Bicycle
11.2.2020...Bicycle
21.2.2020...Bicycle
21.3.2020...Cellphone
21.4.2020...Cellphone
31.1.2020...Chair
31.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!

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Anonymous
Not applicable

Thank you so much! 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.