Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I am relatively new to Power BI and would like to ask for assistance on below challenges
Context: I am creating vessel schedule table however the data I received only has a general port column and I need to arrange it to show respective port pairs (ie. load and discharge ports).
Challenge: I need to determine the value and create a new column (Next Port).
The data I have to work on has the complete fleet and each of these several vessels also have its several voyage numbers.
There is a call order number column and this determines the corresponding next port for the specific voyage number of each vessel.
Data I received :
VESSEL | VOYAGE | BOUND | Call_Order | PORT | Arrival_Date | Departure_Date |
VES 1 | 202001 | S | 1 | PORT A | 12/22/2019 12:00:00 PM | 1/6/2020 8:00:00 PM |
VES 2 | 201929 | N | 2 | PORT B | 1/2/2020 11:42:00 PM | 1/3/2020 8:30:00 AM |
VES 2 | 201929 | N | 3 | PORT D | 1/3/2020 3:00:00 PM | 1/4/2020 1:00:00 AM |
VES 2 | 201929 | N | 4 | PORT E | 1/6/2020 7:40:00 AM | 1/6/2020 8:09:00 PM |
VES 2 | 202001 | S | 1 | PORT A | 1/8/2020 12:00:00 PM | 1/14/2020 7:24:00 AM |
VES 1 | 202001 | N | 2 | PORT E | 1/9/2020 2:06:00 AM | 1/9/2020 11:56:00 AM |
VES 1 | 202001 | N | 3 | PORT B | 1/10/2020 5:20:00 AM | 1/11/2020 11:33:00 AM |
VES 1 | 202002 | S | 1 | PORT A | 1/12/2020 9:44:00 PM | 1/17/2020 6:16:00 PM |
VES 2 | 202001 | N | 2 | PORT B | 1/15/2020 4:25:00 PM | 1/16/2020 4:25:00 AM |
VES 2 | 202001 | N | 3 | PORT E | 1/18/2020 4:15:00 AM | 1/18/2020 4:38:00 PM |
Data I need
VESSEL | VOYAGE | BOUND | Call_Order | PORT | Arrival_Date | Departure_Date | Next Port | |||||
VES 1 | 202001 | S | 1 | PORT A | 12/22/2019 12:00:00 PM | 1/6/2020 8:00:00 PM | PORT E | |||||
VES 2 | 201929 | N | 2 | PORT B | 1/2/2020 11:42:00 PM | 1/3/2020 8:30:00 AM | PORT D | |||||
VES 2 | 201929 | N | 3 | PORT D | 1/3/2020 3:00:00 PM | 1/4/2020 1:00:00 AM | PORT E | |||||
VES 2 | 201929 | N | 4 | PORT E | 1/6/2020 7:40:00 AM | 1/6/2020 8:09:00 PM | PORT A | |||||
VES 2 | 202001 | S | 1 | PORT A | 1/8/2020 12:00:00 PM | 1/14/2020 7:24:00 AM | PORT B | |||||
VES 1 | 202001 | N | 2 | PORT E | 1/9/2020 2:06:00 AM | 1/9/2020 11:56:00 AM | PORT B | |||||
VES 1 | 202001 | N | 3 | PORT B | 1/10/2020 5:20:00 AM | 1/11/2020 11:33:00 AM | PORT A | |||||
VES 1 | 202002 | S | 1 | PORT A | 1/12/2020 9:44:00 PM | 1/17/2020 6:16:00 PM | PORT E | |||||
VES 2 | 202001 | N | 2 | PORT B | 1/15/2020 4:25:00 PM | 1/16/2020 4:25:00 AM | PORT E | |||||
VES 2 | 202001 | N | 3 | PORT E | 1/18/2020 4:15:00 AM | 1/18/2020 4:38:00 PM | PORT D |
Ves 1 (Vessel column) 202001 (Voyage column) has a value of 1 (Call_Order column).
Its next port is determined by filtering the same values of Vessel and Voyage columns and next sequence of Call_Order column which is 2 therefore next port is PORT E.
Translating this logic in Power BI is one of my challenges now.
After determining values of the Next Port column, I still need to create a Transit Time Measure which is the time difference between departure date and time (Port A) and arrival date and time in the corresponding next port (Port E).
Thank you in advance for your help.
Regards,
Dina
Solved! Go to Solution.
Hi, @dnsia
It’s my pleasure to answer for you.
According to your description, I think you can create a measure to calculate the correct result.
First you need to create a index column.
Like this:
Measure =
VAR a =
MINX (
FILTER (
ALL ( 'Table' ),
[VESSEL] = SELECTEDVALUE ( 'Table'[VESSEL] )
&& [Index] > SELECTEDVALUE ( 'Table'[Index] )
),
[Index]
)
RETURN
MAXX (
FILTER (
ALL ( 'Table' ),
[VESSEL] = SELECTEDVALUE ( 'Table'[VESSEL] )
&& [Index] = a
),
[PORT]
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @dnsia
It’s my pleasure to answer for you.
According to your description, I think you can create a measure to calculate the correct result.
First you need to create a index column.
Like this:
Measure =
VAR a =
MINX (
FILTER (
ALL ( 'Table' ),
[VESSEL] = SELECTEDVALUE ( 'Table'[VESSEL] )
&& [Index] > SELECTEDVALUE ( 'Table'[Index] )
),
[Index]
)
RETURN
MAXX (
FILTER (
ALL ( 'Table' ),
[VESSEL] = SELECTEDVALUE ( 'Table'[VESSEL] )
&& [Index] = a
),
[PORT]
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @v-janeyg-msft ,
Thank you so much for your help! It works not only in determining the next port column but the next ports respective arrival and departure dates too.
Regards,
Dina
@dnsia , You can not create a column from a measure. For that you need bucketing, using an independent table and measure
refer: https://www.youtube.com/watch?v=CuczXPj0N-k
or refer:
https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |