Forum Discussion
get value from another table by expression
Hi i have a table Sprints like: id | start date | end date i have another table Issues that has a column that contains date, lets call it changeDate i would like to add to that table a column...
hey,
My both tables look like this
Table1
aDate 2017-01-23 2017-01-15 2017-01-26 2017-02-15
Table2 looks like this
id startdate enddate 1 2017-01-01 2017-01-10 2 2017-01-11 2017-01-21 3 2017-01-22 2017-02-10 4 2017-02-11 2017-02-28
I create a calculated column in Table1 that pulls the id from Table2 from that record where the column aDate is between startdate and enddate (assumption, there is no overlap in the ranges and there are also no gaps in the ranges).
The calculated column
CALCULATE(
MAX('Table2'[id])
,FILTER(
'Table2','Table2'[startdate] <= 'Table1'[aDate] && 'Table2'[enddate] >= 'Table1'[aDate])
)Hope this helps
Regards
thanks, this is whay i needed
but, i do have gaps in the sprint dates, and i assume this is why im getting the same sprint for all rows
You are refering to the DAX solution?
The Power Query solution should work fine. :smileywink:
Hey,
can you please share some sample data.
Regards
