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 opera...
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. 

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?

Anonymous's avatar
Anonymous
Not applicable
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. 

  • virus190's avatar
    virus190
    Helper II
    6 years ago

    Anonymous 

    Thank you very much! This is perfect.

     

    I tried some DAX with RANK and EARLIER, so i wasnt far away for the right solution, but i didnt get it to work properly.

    So you helped me a lot, all i needed.

     

    The last thing i tried was this video:
    https://www.youtube.com/watch?v=-3KFZaYImEY

     

    Last Question:
    What is the better solution in this case? DAX or PQ? What will be the difference? If using PQ it will take longer to load the data in and if using DAX it will take longer to load on the reporting page?