add column based on another column

[rhill]

I have a table with shipments (load number, origin city, destination city, state, date, stop number, etc). My issue is this:

I need to find the LAST destination city for each load. Example: Load #1 had 3 stops: cities A, B & C. My table has 3 entries for Load #1, with each cities pertinent information, and stop # (1, 2, 3). Since it is one load, the origin city for all 3 records is the same. I need to return one record with the load number, origin city and final destination city.

I assume I should add a column that puts the same destination city (the one with the max stop number) on each record, but I cannot figure out how to do that.

Any help is greatly appreciated.

Ronda

[MFelix · Accepted solution]

Hi @rhill,

I believe I understood your request create the following column:

Load Destination City = 
CALCULATE (
 MAX ( Shipments[Destination City] ),
 FILTER (
 ALL ( Shipments[Load Number], Shipments[Stop Number] ,Shipments[Destination City]),
 Shipments[Load Number] = EARLIER ( Shipments[Load Number] )
 )
)

The final result is below check if it what you need:

Regards,

MFelix

[rhill]

that is what I want, but maybe it matters that my load number is really not a "number". They are formatted like GA17-1234, VA17-2345, etc.

[MFelix]

The way the calculation is made it doens matter because I'm comparing the Load value for each cell with the previous one so it works for number or text.

Regards,

MFelix

[rhill]

I think I have entered the code correctly

Load Destination = 
CALCULATE (
    MAX ( COTShippedLoadsOrdersVINs[account_shipping_address_city] ),
    FILTER (
        ALL (
            COTShippedLoadsOrdersVINs[load_name],
            COTShippedLoadsOrdersVINs[stop_number],
            COTShippedLoadsOrdersVINs[account_shipping_address_city]
        ),
        COTShippedLoadsOrdersVINs[load_name]
            = EARLIER ( COTShippedLoadsOrdersVINs[load_name] )
    )
)

But it still does not work

[MFelix]

Do you have any additional field like date or something like that in your table, because I can see you have 3 count of stop number, 4, 7, ... it seems that you have another field that is impacting you calculation and your grouping.

Regards,

MFelix

[rhill]

MFelix,

I couldn't get this to work with my data but I will check when I get time.

Thanks for all you work - I believe your initial response was correct and any issues are on my end. So, I will mark your answer as accepted.

Thanks again,

Ronda