topic Re: Calculated Column based on another table in DAX Commands and Tips

Calculated Column based on another table

neees78 — Fri, 05 Aug 2022 12:28:55 GMT

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

Re: Calculated Column based on another table

Greg_Deckler — Fri, 05 Aug 2022 12:33:39 GMT

neees78 Try:

Column = MAXX(FILTER('Table2', Table1[dt.start] >= Table2[dt.start] && Table1[dt.end] <= Table2[dt.end]), [jobtyp])

Re: Calculated Column based on another table

neees78 — Fri, 05 Aug 2022 20:43:12 GMT

Greg_Deckler thanks a lot - it worked perfectly well

Re: Calculated Column based on another table

neees78 — Sun, 07 Aug 2022 03:51:34 GMT

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