Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
virus190
Helper II
Helper II

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.

virus190_0-1597858340032.png

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

virus190_1-1597858366867.png

 

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?

1 ACCEPTED SOLUTION

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:

1.png

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:

2.png

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:

3.pngYou 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. 

View solution in original post

5 REPLIES 5
v-rzhou-msft
Community Support
Community Support

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:

1.png

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. 

Hi @v-rzhou-msft 

 

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?

 

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:

virus190_0-1597992336098.png

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?

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:

1.png

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:

2.png

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:

3.pngYou 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. 

@v-rzhou-msft 

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.