cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amaleranda
Post Patron
Post Patron

Add a column to a Data table from another lookup table

Hello Gurus,

 

I have a lookup tabel like below(Image1). I need to add a new column (Revision Number) to my continues data tabel(Image2) based on below condition. my date tabel has only one column at the moment it is just the date as below(Image2).

 

Say  all the dates between 29/12/2018 tp 18/01/2019 Revision Number column on the data tabel would be 18W52-03

       all the dates between 19/01/2019 tp 08/02/2019 Revision Number column on the data tabel would be 19W03-06 etc

Image 1

Image1Image1

Image 2

DateTable.PNG

 

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

Hello @amaleranda 

 

Try this:

RevisionNumber = 
CALCULATE(
       VALUES(Image1[Revision Number]),
FILTER(
Image1,
Image2[Date]>= Image1[Revsion_Start_Date]
&&
Image2[Date] <= Image1[Revsion_End_Date]
)
)

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

 

View solution in original post

3 REPLIES 3
vivran22
Community Champion
Community Champion

Hello @amaleranda 

 

Try this:

RevisionNumber = 
CALCULATE(
       VALUES(Image1[Revision Number]),
FILTER(
Image1,
Image2[Date]>= Image1[Revsion_Start_Date]
&&
Image2[Date] <= Image1[Revsion_End_Date]
)
)

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

 

@vivran22  works a magic. thanks mate

 

amitchandak
Super User
Super User

Try to have a new column like

revision no = maxx(filter(table1, table1[start_date]<=table2[Date] &&table1[end date]>=table2[Date]), table1[Revision Number])

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.