Forum Discussion

virus190's avatar
virus190
Helper II
6 years ago
Solved

Reporting FireDepartment Duration until arrived

Hi,

 

i dont get any further, maybe i can get some help here, this is the last thing i need for my reporting.

 

So i created some sample Data. Download the .pbix

 

In one table i have all operations with a timestamp when the signal was recieved.

In the second table i got all resources which were alerted for the operations and when they arrived.

 

Its important for us to keep the limit/deadline. Means:

1. Deadline: The last vehicle from the 1. Group (2x Firetrucks, 1x Turntable Ladder) has to been arrived within 10 minutes.

2. Deadline: The last vehicle from the 2. Group (1x Firetrucks, 1x Turntable Ladder) has to been arrived within 15 minutes.

 

So i want the to know for every operation:

1. How long it took
a) for group 1 to arrive

b) for group 2 to arrive

2. Did we miss the deadline?

a) for group1

b) for group2

 

To explain the solution with the sample data:

 

OperationID 1:

Time of Group1 08:00:00 Signal Recieved the latest vehicle of group 1 arrived at 08:10:00 so it took 10 Minutes

Time of Group2 08:00:00 Signal Recieved the latest vehicle of group 2 arrived at 08:15:00 so it took 15 Minutes

Both Limits wasn't exceeded.

 

OperationID2:
Time of Group1 12:00:00 Signal Recieved the latest vehicle of group 1 arrived at 12:10:00 so it took 10 Minutes
Time of Group2 12:00:00 Signal Recieved the latest vehicle of group 2 DID NOT ARRIVED arrived at 12:15:00 so there is no time for this one

 

OperationID3:
Time of Group1 08:00:00 Signal Recieved the latest vehicle of group 1 arrived at 08:15:00 so it took 15 Minutes

Time of Group2 08:00:00 Signal Recieved the latest vehicle of group 2 arrived at 08:15:00 so it took 15 Minutes

 

Can someone help me with some dax or powerquery? Is this possible?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi virus190 

    I build this column by dax.

    Firstly build a rank column sorted by OperationID and Vehicle.

    RANK = 
RANKX (
    FILTER (
        AlarmedResources,
        AlarmedResources[Vehicle] = EARLIER ( AlarmedResources[Vehicle] )
            && AlarmedResources[OperationID] = EARLIER ( AlarmedResources[OperationID] )
    ),
    AlarmedResources[AlarmedResourcesID],
    ,
    ASC
)

    Then build a combine column:

    CountVehicle = COMBINEVALUES(" ",AlarmedResources[Vehicle],AlarmedResources[RANK])

    Result:

    And Divide the first two Firetrucks , the first Turntable Ladder into Group1 and the third Firetruck, the second Turntable Ladder into Group2.

    Group = 
IF (
    OR (
        AND ( AlarmedResources[Vehicle] = "Firetruck", AlarmedResources[RANK] <= 2 ),
        AND (
            AlarmedResources[Vehicle] = "Turntable Ladder",
            AlarmedResources[RANK] <= 1
        )
    ),
    "Group1",
    IF (
        OR (
            AND ( AlarmedResources[Vehicle] = "Firetruck", AlarmedResources[RANK] = 3 ),
            AND (
                AlarmedResources[Vehicle] = "Turntable Ladder",
                AlarmedResources[RANK] = 2
            )
        ),
        "Group2",
        BLANK ()
    )
)

    Result:

    Finally, build a Statement Column to show whether the Vehicle miss the deadline.

    Statement =
IF (
    ISBLANK ( AlarmedResources[Group] ),
    BLANK (),
    IF (
        AlarmedResources[Group] = "Group1"
            && CALCULATE ( SUM ( AlarmedResources[MinDiff] ) ) <= 10,
        "In DeadLine",
        IF (
            AlarmedResources[Group] = "Group2"
                && CALCULATE ( SUM ( AlarmedResources[MinDiff] ) ) <= 15,
            "In DeadLine",
            "Miss the DeadLine"
        )
    )
)

    Result:

    You can download the pbix file from this link: Reporting FireDepartment Duration until arrived

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi virus190 

    You may try datediff function and if function.

    I build two calculated column to achieve your goal.

     

    MinDiff = 
VAR _Received = CALCULATE(MAX(Operation[Recieved]),FILTER(Operation,Operation[OperationID]=EARLIER(AlarmedResources[OperationID])))
RETURN
DATEDIFF(_Received,AlarmedResources[Arrived],MINUTE)
    Column 2 = IF(AlarmedResources[MinDiff]<=10, "Group1",IF(AlarmedResources[MinDiff]<=15,"Group2","Miss the deadline"))

     

     Result:

    I think the Move off Value and Arrived Value in OperationID 3 is not correct.

    If this reply still couldn't help you solve your problem, please tell me more about your calculate logic to distinguish Group1/Group2, I don't see any value about group. You can provide me with the result you want, or you can share me with your pbix file by your Onedrive for business.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

    • virus190's avatar
      virus190
      Helper II
      6 years ago

      Hi Anonymous 

       

      thank you very much for taking your time to help me.

       

      You are right the timestamps were wrong, corrected it, just refresh the data.

       

      Its getting in the right direction, but not quite there yet.

       

      Definition of group isn't the time it is:
      group1: the first two Firetrucks , the first Turntable Ladder (which arrived)

      group2: the third Firetruck, the second Turntable Ladder (which arrived)

       

      How can this be done?

       

      • virus190's avatar
        virus190
        Helper II
        6 years ago

        i think i got a way, but one step i dont know how to implement.

         

        I want a new column, where a counter for every OperationsID starts to count the same vehicle, looking like this:

        Next step i can do an if statement grouping them in group1 and group2 and then a group by with the latest timestamp.

         

        But how can i do this column in dax/pq i did manually?