Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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.
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.
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.
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.
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.
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |