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 all,
I received 2 tables (Table 1 & 2).
I need to apply the Expenses (Table 1. Expenses columns) on Table 2 as below to produce the bold columns in table 3.
1. Table 2 columns (columns: LOAD container size, laden, empty) need to look up on table 1 columns (port, container size, status columns) and check that they have the same values to apply the correct expense - load (A, B, C) column as shown in table 3 below.
2. This process is to be repeated with same parameters except that it will now refer to the DISCHARGE column instead of LOAD column in table 2. This will produce columns expense - discharge (A, B, C).
Table 1. Expense Matrix
Port | Container Size | Container Status | Expense A | Expense B | Expense C | |||||
PORT 1 | 20s | LADEN | 130 | 1500 | 1350 | |||||
PORT 1 | 20s | EMPTY | 0 | 600 | 1350 | |||||
PORT 1 | 40s | LADEN | 195 | 2750 | 1350 | |||||
PORT 1 | 40S | EMPTY | 0 | 1100 | 1350 | |||||
PORT 2 | 20s | LADEN | 120 | 1000 | 775 | |||||
PORT 2 | 20s | EMPTY | 0 | 400 | 775 | |||||
PORT 2 | 40s | LADEN | 170 | 2000 | 775 | |||||
PORT 2 | 40S | EMPTY | 0 | 800 | 775 |
Table 2. Container Inventory
Container | Container Size | Load | Discharge | LADEN | EMPTY | |||||
CONT 1 | 40s | PORT 1 | PORT 2 | Y | N | |||||
CONT 2 | 40s | PORT 2 | PORT 1 | Y | N | |||||
CONT 3 | 20s | PORT 2 | PORT 1 | Y | N | |||||
CONT 4 | 40s | PORT 1 | PORT 2 | Y | N | |||||
CONT 9 | 20s | PORT 1 | PORT 2 | Y | N |
TABLE 3. The Needed output
Container | Cont Size | Load | Discharge | LADEN | EMPTY | EXPENSE A - LOAD | EXPENSE B - LOAD | EXPENSE C - LOAD | EXPENSE A - DISCHARGE | EXPENSE B - DISCHARGE | EXPENSE C - DISCHARGE |
CONT 1 | 40s | PORT 1 | PORT 2 | Y | N | 195 | 2750 | 1350 | 170 | 2000 | 775 |
CONT 2 | 40s | PORT 2 | PORT 1 | Y | N | 170 | 2000 | 775 | 195 | 2750 | 1350 |
CONT 3 | 20s | PORT 2 | PORT 1 | Y | N | 120 | 1000 | 775 | 130 | 1500 | 1350 |
CONT 4 | 40s | PORT 1 | PORT 2 | Y | N | 195 | 2750 | 1350 | 170 | 2000 | 775 |
CONT 9 | 20s | PORT 1 | PORT 2 | Y | N | 130 | 1500 | 1350 | 120 | 1000 | 775 |
Thank you for your help!
Regards,
Dina
Solved! Go to Solution.
Hi @dnsia ,
According to my understand, you want to get the value from Expense Matrix[A],[B],[C] whose PORT and CONTAINER SIZE matched the same from Container Inventory table, right?
You could use LOOKUPVALUE() function to add columns in Container Inventory table like this:
EXPENSE A-LOAD =
LOOKUPVALUE (
'Expense Matrix'[Expense A],
'Expense Matrix'[Port], [Load],
'Expense Matrix'[Container Size], [Container Size],
'Expense Matrix'[Container Status], "LADEN"
)
EXPENSE B-DISCHARGE =
LOOKUPVALUE (
'Expense Matrix'[Expense B],
'Expense Matrix'[Port], [Discharge],
'Expense Matrix'[Container Size], [Container Size],
'Expense Matrix'[Container Status], "LADEN"
)
My final output as shown below:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @dnsia ,
Take one of them as an example:
EXPENSE A-DISCHARGE Measure =
CALCULATE (
MAX ( 'Expense Matrix'[Expense A] ),
FILTER (
'Expense Matrix',
'Expense Matrix'[Port] = MAX ( 'Container Inventory'[Discharge] )
&& 'Expense Matrix'[Container Size] = MAX ( 'Container Inventory'[Container Size] )
&& 'Expense Matrix'[Container Status] = "LADEN"
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Eyelyn Qin
Hi @dnsia ,
Please try this:
Measure =
SUMX ( 'Container Inventory', [EXPENSE A-DISCHARGE Measure] )
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @dnsia ,
Please try this:
Measure =
SUMX ( 'Container Inventory', [EXPENSE A-DISCHARGE Measure] )
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @dnsia ,
Take one of them as an example:
EXPENSE A-DISCHARGE Measure =
CALCULATE (
MAX ( 'Expense Matrix'[Expense A] ),
FILTER (
'Expense Matrix',
'Expense Matrix'[Port] = MAX ( 'Container Inventory'[Discharge] )
&& 'Expense Matrix'[Container Size] = MAX ( 'Container Inventory'[Container Size] )
&& 'Expense Matrix'[Container Status] = "LADEN"
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Eyelyn Qin
Hi @v-eqin-msft ,
Thank you for your swift response. The total seems to be wrong? Total should should as 785 but is only showing as 170.
Regards,
Dina
Hi @dnsia ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Eyelyn Qin
Hi @v-eqin-msft ,
Works fine. Thank you! Do you have a measure for this instead of calculated column because I will be dealing with thousands of data.
Regards,
Dina
Hi @dnsia ,
According to my understand, you want to get the value from Expense Matrix[A],[B],[C] whose PORT and CONTAINER SIZE matched the same from Container Inventory table, right?
You could use LOOKUPVALUE() function to add columns in Container Inventory table like this:
EXPENSE A-LOAD =
LOOKUPVALUE (
'Expense Matrix'[Expense A],
'Expense Matrix'[Port], [Load],
'Expense Matrix'[Container Size], [Container Size],
'Expense Matrix'[Container Status], "LADEN"
)
EXPENSE B-DISCHARGE =
LOOKUPVALUE (
'Expense Matrix'[Expense B],
'Expense Matrix'[Port], [Discharge],
'Expense Matrix'[Container Size], [Container Size],
'Expense Matrix'[Container Status], "LADEN"
)
My final output as shown below:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |