Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
All -- I am trying to set up a PBI report to show the timings of our nightly processes. There are four processes that we have in our log files with start and end times (assuming it completes, of course).
What I want to get to is to pull how long each process took to complete (if it did), how long did the entire process take to run. And then show the trends over the last 30 days, etc, etc.
My question is how do I best layout this data to make things easier on myself to calculate durations in PBI?
The TIMEs below are formatted "18:54:56.01", "1:46:19.99 ", etc. --- so we may have times that are technically into the next day that the process actually began.
Thanks for your help!
Nightly Process | TIME | Message |
8/28/2020 | Time 1 | Process 1 Start |
8/28/2020 | Time 2 | Process 2 Start |
8/28/2020 | Time 3 | Process 2 Finish |
8/28/2020 | Time 4 | Process 3 Start |
8/28/2020 | Time 5 | Process 3 Finish |
8/28/2020 | Time 6 | Process 4 Start |
8/28/2020 | Time 7 | Process 4 Finish |
Solved! Go to Solution.
@tuta23
I prepared some sample data based on your explanation and came up with the following.
You can download the file: HERE
1. Each Process Duration by Date
2. Entire Process Duration by Date:
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
to just answer you question:
the best layout is the following:
that you can get by pivoting the table on the Start-Finish column after splitting your message column :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3AiIDIwMlHaWQzNxUBUMgI6AoPzm1uFjBUCG4JLGoRClWB4tCIySFRvgUGqModMvMyyzOwK7SBEmlMT4jTVEU4jPSDEmlCT4jzVEUwoyMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Nightly Process" = _t, TIME = _t, Message = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Nightly Process", type text}, {"TIME", type text}, {"Message", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Message", Splitter.SplitTextByRepeatedLengths(10), {"Message.1", "Message.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Message.1", type text}, {"Message.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Message.2]), "Message.2", "TIME")
in
#"Pivoted Column"
I marked Fowby as solution but there is good stuff to be learned in each reply.
Thanks to all!
@tuta23
I prepared some sample data based on your explanation and came up with the following.
You can download the file: HERE
1. Each Process Duration by Date
2. Entire Process Duration by Date:
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@tuta23 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
Now, since it sounds like you are dealing with duration at some point, see Chelsie Eiden's Duration as you may need it. https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
@ me in reply if you have any issues.
Thanks Greg. I think I need a little more help to get to that point.
I think my data needs to get into a better format in order to calculate durations.
Say, something like this:
Nightly Process | Process 1 Start | Process 2 Start | Process 2 Finish | Process 3 Start | Process 3 Finish | Process 4 Start | Process 4 Finish |
8/28/2020 | DateTime 1 | DateTime 2 | DateTime 3 | DateTime 4 | DateTime 5 | DateTime 6 | DateTime 7 |
8/29/2020 | DateTime 1 | DateTime 2 | DateTime 3 | DateTime 4 | DateTime 5 | DateTime 6 | DateTime 7 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |