Please help with measure returning a date.time column that is filtered from another text column

[drogzy]

Hi all, 

I have to two tables:

1) header 

 2) job

I have a relationship of many to one where for many job rows (usually 2-3) there is one header row, cross filter direction both. 

Currently my data is a direct query to a SQL database so there are limitations to what I can do in terms of adding custom column. 

I the following columns in my job table (a summary of):

Start of job	End of job	Type of job
02/10/2022 8:00AM	02/16/2022 8:00PM	Drilling
03/13/2022 7:00AM	03/17/2022 6:00PM	Production
01/05/2022 6:00AM	01/20/2022 5:00 PM	Abandon

My visualization or end goal currently is a bigger table with many columns coming from various tables

What I am tring to do: Create a measure that will return a date.time column filtered with the specific type of start and end job, for example: Start of drilling job; End of drilling job. I would essentially do this for every type of job...Start of production job, End of production job etc. After I got the dates for each job type, I will do another measure where I need the hour difference between the start and end of each job type. 

What I have tried to do: Create a custom date.time column in power query but get the following error due to the direct query: 

Thanks for your time 

[tackytechtom · Accepted solution]

Hi @drogzy ,

Is it this you are looking for? 

This is the code I used:

if [Type of job] = "Drilling" then [Start of job] else null

Afterwards, I changed the data type to datatime.

Does this work for you? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[drogzy]

Thank you @tackytechtom, I totally overthought this. 

Cheers!