Forum Discussion
Solved
Calculated Column based on another table
Hello All,
I've two tables in my project - no relationship exists
Table 1
| dt.start | dt.end | jobtyp |
| 13/06/2022 00:15 | 13/06/2022 01:30 | Travel |
| 17/06/2022 17:45 | 18/06/2022 10:00 | Travel |
| 28/06/2022 23:15 | 28/06/2022 23:30 | Travel |
| 07/07/2022 03:30 | 07/07/2022 06:00 | Travel |
| 24/07/2022 07:30 | 24/07/2022 12:00 | Logging |
| 02/08/2022 18:30 | 02/08/2022 20:00 | Logging |
| 02/08/2022 20:30 | 02/08/2022 23:00 | Logging |
Table2
| jobtyp | dt.start | dt.end |
| Travel | 03/06/2022 11:00 | 15/07/2022 00:00 |
| Logging | 22/07/2022 06:30 | 03/08/2022 23:00 |
on Table1 - is it possible to have jobtyp column calculated based on Table2 jobtyp value
using date values only
if Table1(dt.start) >= Table2(dt.start) && Table1(dt.end)<= Table2(dt.end)
then return jobtyp value from Table2
Thanks in advance
neees78 Try:
Column = MAXX(FILTER('Table2', Table1[dt.start] >= Table2[dt.start] && Table1[dt.end] <= Table2[dt.end]), [jobtyp])
3 Replies
neees78 Try:
Column = MAXX(FILTER('Table2', Table1[dt.start] >= Table2[dt.start] && Table1[dt.end] <= Table2[dt.end]), [jobtyp])Greg_Deckler thanks a lot - it worked perfectly well
Greg_Deckler if I have an ID column in both tables, can we add a condition or second filter to make sure it returns correct [jobtyp] or that row, what I found is that some jobs do cross over in times, thus it returns the first [jobtyp] within that time interval.
regards
