G'day!
Probably one of the most popular topics - I've read a ton of articles but I seem to be a blockhead today. Anyhow, I hope you can point me in the right direction with this rather simple challenge.
Table A:
| DECK_TYPE | DECK_HEIGHT |
| CAR | 1.7 |
| CAR | 2.0 |
| HSV | 2.8 |
| HHO | 3.2 |
| HHO | 3.8 |
| HHO | 4.3 |
| HHM | 6.5 |
Table B:
| CARGO_CLASS | HEIGHT | SQM |
| AUTOS | 1.5 | 9 |
| AUTOS | 1.85 | 10 |
| H&H | 2.8 | 31 |
| H&H | 3.4 | 25 |
| BB | 5.7 | 43 |
Problem description
Table A describes the capacity of a cargo carrier while Table B describes the cargo that shall be carried. My challenge is, that I need to apply a rather simple "least cost/effort" calculation to figure out how the different decks are utilized based on the currently booked cargo. In my Power Apps solution, I calculate a sorting order, where CAR has the lowest multiplicator and HHM the highest. That figure is multiplied with the DECK_HEIGHT and the first value of that calculated and sorted table is used to virtually assign the cargo. I can't pull that off in Power BI...
Following logic applies:
| DECK_TYPE | Can carry following CARGO_CLASS |
| CAR | AUTOS |
| HSV | AUTOS |
| HHO | AUTO, H&H, BB |
| HHM | AUTO, H&H, BB |
Desired Output
| DECK_TYPE | DECK_HEIGHT | BOOKED_CARGO_SQM |
| CAR | 1.7 | 9 |
| CAR | 2.0 | 10 |
| HSV | 2.8 | 0 |
| HHO | 3.2 | 31 |
| HHO | 3.8 | 25 |
| HHO | 4.3 | 0 |
| HHM | 6.5 | 43 |
Can you please help me, to assemble the measure for BOOKED_CARGO_SQM?
Thank you in advance,
Henning
