cancel
Showing results 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

Helper I

## Get Id from second table based on date in first table and date range in second table

I have a hire date in my first table (T1), and I need to create a calculated column in T1 that gets the ID value from a second table (T2), based on the date range in T2. To illustrate, here is some sample data from T1:

And here is the data from T2:

What I need to do is create a calculated column for T1 that finds the date range T2.ProgramStartDate to T2.ProgramEndDate that contains T1.HireDate, and then returns T2.ID to that calculated column (if you compare the hire dates in T1 to the date ranges for the programs in T2, you can see that the corresponding ProgramIDs have been added to T1):

Does anyone know how I can do this?

Thanks.

1 ACCEPTED SOLUTION
Helper I

Got it. It actually turned out to be very simple:

ProgramID = CALCULATE(VALUES(T2[ID]), FILTER(T2,T2[ProgramStartDate]<=T1[HireDate]&&T2[ProgramEndDate]>=T1[HireDate]))

Don't know why it took me almost two hours, and multiple, far more convoluted formulae to figure it out, but this did the trick for me.

Helper I

Got it. It actually turned out to be very simple:

ProgramID = CALCULATE(VALUES(T2[ID]), FILTER(T2,T2[ProgramStartDate]<=T1[HireDate]&&T2[ProgramEndDate]>=T1[HireDate]))

Don't know why it took me almost two hours, and multiple, far more convoluted formulae to figure it out, but this did the trick for me.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors