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
dnsia
Helper II
Helper II

Create new column and measure based on values of different columns

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 : 

VESSELVOYAGEBOUNDCall_OrderPORTArrival_DateDeparture_Date
VES 1202001S         1PORT A12/22/2019 12:00:00 PM1/6/2020 8:00:00 PM
VES 2201929N         2PORT B1/2/2020 11:42:00 PM1/3/2020 8:30:00 AM
VES 2201929N         3PORT D1/3/2020 3:00:00 PM1/4/2020 1:00:00 AM
VES 2201929N         4PORT E1/6/2020 7:40:00 AM1/6/2020 8:09:00 PM
VES 2202001S         1PORT A1/8/2020 12:00:00 PM1/14/2020 7:24:00 AM
VES 1202001N         2PORT E1/9/2020 2:06:00 AM1/9/2020 11:56:00 AM
VES 1202001N         3PORT B1/10/2020 5:20:00 AM1/11/2020 11:33:00 AM
VES 1202002S         1PORT A1/12/2020 9:44:00 PM1/17/2020 6:16:00 PM
VES 2202001N         2PORT B1/15/2020 4:25:00 PM1/16/2020 4:25:00 AM
VES 2202001N         3PORT E1/18/2020 4:15:00 AM1/18/2020 4:38:00 PM

 

Data I need 

VESSEL VOYAGE BOUND Call_Order PORTArrival_DateDeparture_Date Next Port
VES 1 202001 S          1 PORT A12/22/2019 12:00:00 PM1/6/2020 8:00:00 PM PORT E
VES 2 201929 N          2 PORT B1/2/2020 11:42:00 PM1/3/2020 8:30:00 AM PORT D
VES 2 201929 N          3 PORT D1/3/2020 3:00:00 PM1/4/2020 1:00:00 AM PORT E
VES 2 201929 N          4 PORT E1/6/2020 7:40:00 AM1/6/2020 8:09:00 PM PORT A
VES 2 202001 S          1 PORT A1/8/2020 12:00:00 PM1/14/2020 7:24:00 AM PORT B
VES 1 202001 N          2 PORT E1/9/2020 2:06:00 AM1/9/2020 11:56:00 AM PORT B
VES 1 202001 N          3 PORT B1/10/2020 5:20:00 AM1/11/2020 11:33:00 AM PORT A
VES 1 202002 S          1 PORT A1/12/2020 9:44:00 PM1/17/2020 6:16:00 PM PORT E
VES 2 202001 N          2 PORT B1/15/2020 4:25:00 PM1/16/2020 4:25:00 AM PORT E
VES 2 202001 N          3 PORT E1/18/2020 4:15:00 AM1/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

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

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]
    )

v-janeyg-msft_0-1604026779328.png

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.

View solution in original post

3 REPLIES 3
v-janeyg-msft
Community Support
Community Support

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]
    )

v-janeyg-msft_0-1604026779328.png

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


amitchandak
Super User
Super User

@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

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.