Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
drogzy
Helper I
Helper I

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

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 jobEnd of jobType of job
02/10/2022 8:00AM02/16/2022 8:00PMDrilling
03/13/2022 7:00AM03/17/2022 6:00PMProduction
01/05/2022 6:00AM01/20/2022 5:00 PMAbandon

 

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: 

 

drogzy_1-1647672849920.png

 

drogzy_0-1647672693282.png

Thanks for your time 

 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @drogzy ,

 

Is it this you are looking for? 

tomfox_0-1647674902227.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

2 REPLIES 2
tackytechtom
Super User
Super User

Hi @drogzy ,

 

Is it this you are looking for? 

tomfox_0-1647674902227.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Thank you @tackytechtom, I totally overthought this. 

Cheers!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.