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.
Hello there,
I have been trying to come up with a formula that will calculate how many incidents were recorded in total per day. The following info is what I have:
I have been manually entering the Incident # (but I am sure that power BI can do this for me :)).
It is considered as 1 incidents all recordings between 1 - 2minutes apart. For example truck#9403 recorded 2 counts (less than 1min apart), those two counts are considered as 1 incident.
If you can help me with this, I will really appreciate it.
Thanks!
Solved! Go to Solution.
Hi @YessikaLp ,
You can create two calculated columns as below to get it, please find the details in the attachment.
Time Diff>1 =
VAR _pretime =
CALCULATE (
MAX ( 'Table'[Column1] ),
FILTER (
'Table',
'Table'[Day] = EARLIER ( 'Table'[Day] )
&& 'Table'[Column1] < EARLIER ( 'Table'[Column1] )
)
)
VAR _timediff =
DIVIDE ( DATEDIFF ( _pretime, 'Table'[Column1], SECOND ), 60, 0 )
RETURN
IF ( ISBLANK ( _pretime ), 1, IF ( _timediff >= 1, 1, 0 ) )
Incident # =
CALCULATE (
SUM ( 'Table'[Time Diff>1] ),
FILTER ( 'Table', 'Table'[Column1] <= EARLIER ( 'Table'[Column1] ) )
)
Best Regards
Hi @YessikaLp ,
You can create two calculated columns as below to get it, please find the details in the attachment.
Time Diff>1 =
VAR _pretime =
CALCULATE (
MAX ( 'Table'[Column1] ),
FILTER (
'Table',
'Table'[Day] = EARLIER ( 'Table'[Day] )
&& 'Table'[Column1] < EARLIER ( 'Table'[Column1] )
)
)
VAR _timediff =
DIVIDE ( DATEDIFF ( _pretime, 'Table'[Column1], SECOND ), 60, 0 )
RETURN
IF ( ISBLANK ( _pretime ), 1, IF ( _timediff >= 1, 1, 0 ) )
Incident # =
CALCULATE (
SUM ( 'Table'[Time Diff>1] ),
FILTER ( 'Table', 'Table'[Column1] <= EARLIER ( 'Table'[Column1] ) )
)
Best Regards
Thank youfor the answer!! very very helpul, but... I have another question.
The following example Truck #1 had an incident at 9:03:50 and less than 1 min another truck had another incident. What can I add to the formula so that identifies that it is another truck which is having an incident and separate the incidents because the formula I have is considering but truck as 1 incident.
Thank you in advance for the help!!!
Hi @YessikaLp ,
I updated the sample pbix file(see the attachement), please check if that is what you want.
Best Regards
@YessikaLp , Create a new flag column
=
var _max = maxx(filter(Table, [Trucks] = earlier([Truck]) && [Date] = earlier([Date]) && [Time] < earlier([Time]) ), [Time])
return
Switch(True(),
isblank(_max) , blank() ,
datediff(_max, [Time], minute) <2, blank() ,
[Time]
)
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |