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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Anonymous
Not applicable

Thank you so much! 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.