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.
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?
Solved! Go to 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:
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.
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.
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:
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:
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.
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?
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 |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |