Calculate values based on multiple columns of two tables

[dnsia]

Hi all, 

I need to determine the expenses of each containers. I have two tables.

Table 1. Consist of list of container numbers that was loaded on a specific port. 

Container_No		Equipment_Type		Port_of_Loading		LODF		LODE
ABC001		20DC		PORT C		4/17/2020 10:53:00 AM		null
ABC002		40DC		PORT C		null		9/25/2020 11:31:00 PM
ABC003		20DC		PORT B		11/4/2020 5:45:00 AM		null
ABC004		40DC		PORT B		null		2/2/2020 7:40:00 PM
ABC005		20DC		PORT A		4/17/2020 10:53:00 AM		null

Table 2. Contains the tariff of each specific cargo expense that is to be applied for each container in table 1.

Port		Size Type		Status		Cost Activity	Amount
PORT A		20s		LDD		Wharfage	125
PORT B		20s		MT		Wharfage	0
PORT A		40s		LDD		Wharfage	190
PORT B		40s		MT		Wharfage	0
PORT C		20s		LDD		Arrastre	905
PORT C		20s		MT		Arrastre	350
PORT C		40s		LDD		Arrastre	1500
PORT C		40s		MT		Arrastre	700
PORT B		20s		MT		Cranage	1300
PORT B		40s		LDD		Cranage	1300

As you may have noticed, there is no status in Table 1 to compare it to. Instead there is LODF/LODE column. 

If there is value in LODF, container is LDD. If there is value in LODE, container is MT.

I need to apply each cost activity(Table 2. Cost Activity column) to each containers (Table 1. Container column) using this data set. Please help.

Container_No		Equipment_Type		Port_of_Loading		LODF		LODE	Wharfage	Arrastre	Cranage
ABC001		20DC		PORT C		4/17/2020 10:53:00 AM		null	0	905	0
ABC002		40DC		PORT C		null		9/25/2020 11:31:00 PM	0	700	0
ABC003		20DC		PORT B		11/4/2020 5:45:00 AM		null	0	0	0
ABC004		40DC		PORT B		null		2/2/2020 7:40:00 PM	0	0	0
ABC005		20DC		PORT A		4/17/2020 10:53:00 AM		null	0	0	0

Would be good if this can be done in DAX instead of calculated column. 


Thank you,
Dina

[lbendlin · Accepted solution]

You may want to consider adding composite keys to both tables, consisting of size, port and status.  Then you can link the tables via that key and create your matrix visual as desired.

[V-pazhen-msft · Accepted solution]

@dnsia 

I am not clear with how do you want to match the table 2 amount with table 1 container, the idea is transform table 2 using pivot, you may check the steps in the query editor in the pbix.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

You may want to consider adding composite keys to both tables, consisting of size, port and status.  Then you can link the tables via that key and create your matrix visual as desired.