Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! | |
#proudtobeasuperuser | |
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! | |
#proudtobeasuperuser | |
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
7 |