Conditional column: Date in one table lookup with date ranges in different tables for every record.

[Anonymous]

I have a Table1:

JobName	Timestamp
A	21-04-2019 12:30
G	22-04-2019 12:30
FF	23-04-2019 12:30
S	24-04-2019 12:30
E	25-04-2019 12:30
G	26-04-2019 12:30
R	27-04-2019 12:30
H	28-04-2019 12:30
RE	29-04-2019 12:30
D	30-04-2019 12:30
A	21-04-2019 12:30
D	21-04-2019 12:31
G	21-04-2019 12:32
FF	21-04-2019 12:33
R	17-04-2019 12:34
C	21-04-2019 12:35
B	18-04-2019 12:36
S	21-04-2019 12:37
S	21-04-2019 12:38
FF	21-04-2019 12:39

I have another Table2:

Key	JobName1	JobName2	Jobname3	Jobname4	StartTime	EndTIme
1	A			S	20-04-2019 12:30	20-04-2019 13:20
2	B				16-04-2019 12:30	21-04-2019 13:20
3	C	A		G	17-04-2019 12:30	22-04-2019 13:20
4	D	G	S		18-04-2019 12:30	23-04-2019 13:20
5	S		D	G	19-04-2019 12:30	24-04-2019 13:20
6	R	S	A		20-04-2019 12:30	25-04-2019 13:20
7	A	D	F		21-04-2019 12:30	26-04-2019 13:20
8	G	RE		A	20-04-2019 04:30	20-04-2019 07:20
9	H	F	A	D	23-04-2019 12:30	23-04-2019 13:20
10	E	E	G	F	18-04-2019 07:30	18-04-2019 09:20
11	SS				25-04-2019 12:30	25-04-2019 13:20
12	FF		A	G	26-04-2019 12:30	26-04-2019 13:20
13	S			A	17-04-2019 10:30	17-04-2019 11:52
14	S		G		28-04-2019 23:30	29-04-2019 00:20
15	FF	D		A	29-04-2019 12:30	29-04-2019 13:20
16	R			G	20-04-2019 12:30	20-04-2019 13:20
17		G			18-04-2019 12:30	18-04-2019 13:20
18					02-05-2019 12:30	02-05-2019 13:20
19		H			20-04-2019 17:30	20-04-2019 19:23
20			D	S	17-04-2019 14:30	17-04-2019 15:20

I want to add another column in Table1:

• Flag=1 if Timestamp for that Job lies in any of the Time period defined in Table2 against that job (in any of the 4 Jobnames in Table2).
• Flag=0 otherwise.

I tried to be as clear as possible.

I have this data in a relational DB. So I can't go with modifying the source itself after doing the same in python/dataframe.

I tried to employ the Python scripting in PowerQuery, but I'm not able to be successful with loading tow tables at once.

I need to do it in DAX/Power Query, since I have to do it in PowerBI itself!

[JarroVGIT]

First off, interesting Question! I've loaded your tables in PBI and got the desired result.

Step 1. You need to unpivot the columns JobName1 through JobName4 in Table2. If you require this table to be unchanged in your model, copy your query so you have a duplicate and then unpivot the columns. This results in a Table where column Attribute contains either JobName1 through JobName 4 with a corresponding Value column of the jobnames. 

Step 2. We're not interested in the Attributes column for this purpose, so delete it. Rename Value column to JobName. Filter out the blanks of column JobName.

Outcome: we now have transformed Table2 into a table with jobnames and their corresponding beginTime and endTIme. 

Step 3: Add a calculated column in Table1, with the following formula:

Flag = IF(CALCULATE(COUNTA(Table2[Key]), FILTER(Table2, (Table1[Timestamp] >= Table2[StartTime] && Table1[Timestamp] <= Table2[EndTIme] && Table1[JobName] = Table2[JobName]))) > 0, TRUE, FALSE)

Explanation: For every row in Table1, we are doing a rowcount of Table2 where the timestamp and jobname are the same. If the row count is bigger than 0, then return TRUE (or 0 in your case, I used TRUE) and when the row count is zero then return FALSE.

This results in the following table:

JobName Timestamp Flag

A	21-04-2019 12:30	True
G	22-04-2019 12:30	True
FF	23-04-2019 12:30	False
S	24-04-2019 12:30	True
E	25-04-2019 12:30	False
G	26-04-2019 12:30	True
R	27-04-2019 12:30	False
H	28-04-2019 12:30	False
RE	29-04-2019 12:30	False
D	30-04-2019 12:30	False
A	21-04-2019 12:30	True
D	21-04-2019 12:31	True
G	21-04-2019 12:32	True
FF	21-04-2019 12:33	False
R	17-04-2019 12:34	False
C	21-04-2019 12:35	True
B	18-04-2019 12:36	True
S	21-04-2019 12:37	True
S	21-04-2019 12:38	True
FF	21-04-2019 12:39	False

Note: some jobs in Table1 have overlapping timeframes with multiple rows in Table2. You can check this by changing the formula to removing the IF statement and '> 0, TRUE, FALSE)'  part. Hope this helps! 

Please check this as solution if this answered your question.