Forum Discussion

bmacman's avatar
bmacman
Frequent Visitor
3 years ago

Create new column based on value from another column with a specific date range and matching ID

Hi all, Trying to create a new column in a table. This column needs get data from another table with a time +/- 30 seconds from Table1. Table1 already has a minute for every minute of the day colum...
Need Help
amitchandak's avatar
amitchandak
Super User
3 years ago

bmacman , a new column

 

=

var _max = maxx(Filter(Table2, 'Table2'[minutesAddedToStartDate] >= 'Table1'[minuteAddedToStartDate]+ #duration(0,0,0,30) && 'Table2'[minutesAddedToStartDate] <'Table1'[minuteAddedToStartDate]-#duration(0,0,0,30) ),
'Table2'[mode2])

 

return

if(isblank(_max), 'Table1'[mode1],_max)

 


refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

bmacman's avatar
bmacman
Frequent Visitor
3 years ago

Hi amitchandak, Thanks for the response

Tried this, gives me this error. which is the # at the start of the #duration

 

  • bmacman's avatar
    bmacman
    Frequent Visitor
    3 years ago

    It actually worked first run then tried to change the name of the merge query table and this error appeared