Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I would like some help to build a DAX formula that would collect for each day the earliest event - for each of the L.
Also another similar formula the latest event, in a manner I would be able to subtract the two and find the number of hours the line operates in each particular day..
Initially I was able to obtain it via MEASUREMENT, however, they do not how in the Format table and I could not create a subtraction formula between two measurements, reason I prefer a new column.
Thanks,
StartTime StopTime LineNum
4/3/2020 17:17 4/3/2020 17:17 3
4/3/2020 17:12 4/3/2020 17:12 1
4/3/2020 17:09 4/3/2020 17:09 2
4/3/2020 17:06 4/3/2020 17:06 3
4/3/2020 17:01 4/3/2020 17:01 2
4/3/2020 16:58 4/3/2020 16:58 3
4/3/2020 6:28 4/3/2020 6:28 2
4/3/2020 5:27 4/3/2020 5:27 3
4/3/2020 4:27 4/3/2020 4:27 1
4/2/2020 20:12 4/2/2020 20:16 3
4/2/2020 20:10 4/2/2020 20:14 1
4/2/2020 20:09 4/2/2020 20:14 2
4/2/2020 8:27 4/3/2020 8:40 1
4/2/2020 8:37 4/2/2020 8:39 2
4/2/2020 7:15 4/2/2020 7:18 3
Solved! Go to Solution.
@Anonymous I think I got your question, see attached pbix file. There are many tables in it, just focus on Line table and a page called Line.
You can take it from here and tweak as per your need. Would appreciate Kudos 🙂 if my solution helped.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous ,
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
Hi Amit,
Sorry if my explanation was not clear.
See the example below, where per day and per each Line, we record the first and last event, so the date difference can be subtracted.
LineNum Date Start Time End Time # of Hours
1 4/2/2020 4/2/20 8:27 AM 4/3/20 8:40 AM 24
2 4/2/2020 4/2/20 8:37 AM 4/2/20 8:14 PM 12
3 4/2/2020 4/2/20 7:15 AM 4/2/20 8:16 PM 13
1 4/3/2020 4/3/20 4:27 AM 4/3/20 5:12 PM 13
2 4/3/2020 4/3/20 6:28 AM 4/3/20 5:09 PM 11
3 4/3/2020 4/3/20 5:27 AM 4/3/20 5:17 PM 12
I got a solution already as per the second response, but do appreciate your help.
In the same kind of subject, one thing you can help.
There is any way we can replicate a "MEASUREMENT" in the Data Table (Edition) in a manner that it would show for us there and allow us to make other calculations with a measurement result?
@Anonymous I think I got your question, see attached pbix file. There are many tables in it, just focus on Line table and a page called Line.
You can take it from here and tweak as per your need. Would appreciate Kudos 🙂 if my solution helped.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi Parry,
your example was sufficient and helped me reach a solution for the problem.
The only other related tip would be in the # hours result, we can an Integer number for hours. I can make it Minute also, but wha trick could we use to get HH: MM in the DIFF formula?
regards,
Leo
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |