How to insert a conditional row

[dnsia]

Hi all, 

I am trying to calculate the total number of containers loaded for each vessel / voyage but it is not showing the number of containers that came from transshipment (airline term: layover). 

I have 4 datasets for this. 

1. Volume- This lists only the direct shipment ( in airline term, direct flight) but not w/ transshipment (layover).

BL Number

EQPID

Vessel

Voyage

Bound

Load Port

Discharge Port

BL01

Container1

VES1

1

N

PORT1

PORT3

2. Load -  all the containers that are confirmed to have departed the origin (PORT1) and with the final destination (PORT3).

BL Number

Container_No

Load Port

Discharge Port

Vessel

Voyage

Bound

BL01

Container1

PORT1

PORT3

VES1

1

N

3. Transshipment (Layover) - list the container being discharged and loaded to another vessel to reach the final destination port. 

BL Number

Container_No

Load Transhipment

Discharge Transhipment

VSLCode

VoyCode

Bound

BL01

CONTAINER1

PORT2

VES2

2

S

BL01

CONTAINER1

PORT2

VES1

1

N

4. Destination - Lists all containers that have confirmed to reach the final destination (PORT3).

BL Number

Discharge Port

Container_No

Vessel

Voyage

Bound

Load Port

BL01

PORT3

CONTAINER1

VES2

2

S

PORT1

Desired Output is for Table 1 to be like below

BL Number

EQPID

Vessel

Voyage

Bound

Load Port

Discharge Port

Load Transhipment

Discharge Transhipment

BL01

Container1

VES1

1

N

PORT1

PORT3

NULL

PORT 2

BL01

Container1

VES2

2

S

PORT1

PORT3

PORT2

NULL

Thanks a lot in advance for your help! 



Regards,

Dina

[v-yangliu-msft · Accepted solution]

Hi  @dnsia  ,

Here are the steps you can follow：

1. Create calculated table.

Table =
GENERATEALL(
    'Transshipment (Layover)',
    var _table1BL='Transshipment (Layover)'[BL Number]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('Destination','Destination'[BL Number]=_table1BL),
        "Load port",'Destination'[Load Port]))

Table 2 =
GENERATEALL(
    'Table',
    var _table1BL='Table'[BL Number]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('Volume','Volume'[BL Number]=_table1BL),
        "Discharge Port",'Volume'[Discharge Port]))

2. Result：

You can downloaded PBIX file from here

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!

[Syndicate_Admin]

Yes, this works!

Thank you!