How to create a column based off the values in other columns

[Ugot2Bsh1tingMe]

Hello,

I have a table that contains, among other things, order numbers, and order stop sequences. What I would like to achieve is to create a column that distinguishes based on stop sequence if that stop is the origin, destination, or stop-off. Determining the origin is easy because the stop sequence is always 1. Attempts to identify the destination, though, have not been successful. Example 1 below shows some mock data with three different order numbers but the number of stops varies. Example 2 is what I would like to achieve if possible. Any help would be greatly appreciated. 

Example 1:

Order Number	Order Stop Sequence
101221	1
101221	2
101221	3
444222	1
444222	2
333111	1
333111	2
333111	3
333111	4

Example 2:

Order Number	Order Stop Sequence	Type of Stop
101221	1	Origin
101221	2	Stop Off
101221	3	Destination
444222	1	Origin
444222	2	Destination
333111	1	Origin
333111	2	Stop Off
333111	3	Stop Off
333111	4	Destination

[Ugot2Bsh1tingMe]

Thank you all for your help! Both solutions listed did work. Mahenkj2, thank you for pointing out some information that would have clarified my request. I'll (try to, lol) be sure and be more clear in future requests. Again, thanks to all. 

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = if(Data[Order Stop Sequence]=1,"Origin",if(CALCULATE(MAX(Data[Order Stop Sequence]),FILTER(Data,Data[Order Number]=EARLIER(Data[Order Number])))=Data[Order Stop Sequence],"Destination","Stop off"))

Hope this helps.

[v-kaiyue-msft · Accepted solution]

Hi @Ugot2Bsh1tingMe ,

Thanks to @mahenkj2  for the reply, please allow me to provide another insight:
You can create a calculated column and then use the switch function to get the desired result.

Column =
VAR _max = CALCULATE(MAX('Table'[Order Stop Sequence]),FILTER(ALL('Table'),'Table'[Order Number] = EARLIER('Table'[Order Number])))
RETURN
SWITCH(TRUE(),
'Table'[Order Stop Sequence] = 1,"Origin",
'Table'[Order Stop Sequence] = _max,"Destination",
"Stop Off")

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[mahenkj2]

Hi @Ugot2Bsh1tingMe ,

So type of stop does not depend on order stop sequence, 1 is always origin, but 2 can be destination or stop off, likewise 3 can also be stop off or destination.

So then type of stop dependent on what, order number? Pls confirm logic clearly.