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
I've been trying to solve my problem for a few days, but even with research on the forum, I can't seem to make it work.
Concept :
I get pings from 7 machines at varying delays. These looks like this :
I am trying to calculate the availability of these machines in the last days. To date, I created a pie-chart that looked at the number of ping occurrences for each of the machine statuses (0-2-3-4-5-6, meaning stopped, running, in alarm, etc). The problem is that if the machine pings status "6" 60 times (every minute) for an hour, then pings status "2" once and then goes an hour without a ping, the pie-chart will show that status "6 " takes up 60 times more space, when it should be equal.
So I need to calculate the difference (in seconds) of time between two lines, per machine, since the last ping. Like this :
I tried a couple of measure and calculated column, and the closest I got is this :
TimedifferenceMeasure =
VAR CurrentIndex =
MAX ( machine_status[id] )
VAR PreviousIndex =
CALCULATE (
MAX ( machine_status[id] ),
FILTER ( machine_status, machine_status[id] < CurrentIndex )
)
VAR difference =
DATEDIFF (
CALCULATE (
MAX ( machine_status[Creation Time] ),
FILTER ( ALL ( machine_status ), machine_status[id] = PreviousIndex )
),
CALCULATE (
MAX ( machine_status[Creation Time] ),
FILTER ( ALL ( machine_status ), machine_status[id] = CurrentIndex )
),
SECOND
)
RETURN
IF ( difference > 43200, 43200, difference )
(The purpose of the 43200 at the end is to limit the time per status to 12 hours. Skip that, the machine is usually closed)
Here's the link to my file :
Thank you !
Solved! Go to Solution.
Hi @Anonymous ,
Does this one here help you?
Ranking Column in your case should be the [Created On] column, where as the Grouping Column is [id]
DateDiffColumnWithoutIndex = DATEDIFF ( 'TableName'[RankingColumn], CALCULATE ( MIN ( 'TableName'[RankingColumn] ), FILTER ( 'TableName', 'TableName'[RankingColumn] > EARLIER ( 'TableName'[RankingColumn] ) ), FILTER ( 'TableName', 'TableName'[GroupingColumn] = EARLIER ( 'TableName'[GroupingColumn] ) ) ), SECOND )
Doing this in a calculated column might take quite some resources and time. Maybe consider adding this column in Power Query instead. This one here, might help you in that case.
Let me know if this helps!
/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 | |
Thanks tackytechtom !
I ended up doing it in Power Query like in your link. A couple changes to adapt to my situation and it works now !
Hi @Anonymous ,
Does this one here help you?
Ranking Column in your case should be the [Created On] column, where as the Grouping Column is [id]
DateDiffColumnWithoutIndex = DATEDIFF ( 'TableName'[RankingColumn], CALCULATE ( MIN ( 'TableName'[RankingColumn] ), FILTER ( 'TableName', 'TableName'[RankingColumn] > EARLIER ( 'TableName'[RankingColumn] ) ), FILTER ( 'TableName', 'TableName'[GroupingColumn] = EARLIER ( 'TableName'[GroupingColumn] ) ) ), SECOND )
Doing this in a calculated column might take quite some resources and time. Maybe consider adding this column in Power Query instead. This one here, might help you in that case.
Let me know if this helps!
/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 |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |