Determine next step based on latest information

[joshua1990]

Hi all!

I have a table that shows me the current state per order:

Order Number	Article	State 1	State 2	Quantity
5556	Bike 1	60	1	500
5557	Bike 2	50	3	600

• State 1: This columns shows the process step sequence
• State 2: This columns contains values 1,2 or 3.
  • 1: Started
  • 2: In Progress
  • 3: Finished

Then I have a second table that shows me for each article the necessary steps:

Article	State 1	Description
Bike 1	20	Assembly
Bike 1	50	Drill
Bike 1	60	Mount
Bike 1	20	Assembly
Bike 1	30	Drill

Now I would like to calculate the next step (calculated column) in the first table based on this conditions:

• If State 2 = 1 or 2, then take Description of table 2
• If State 2 = 3, then take the Description if table 2 for the next state 1.
  
  • If the last state was reached, then display "Finished".

How would you do that in DAX?

[v-yiruan-msft]

Hi  @joshua1990,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[ValtteriN]

Hi, 

@v-yiruan-msft 's solution should help you to solve your issue. Another interesting approach to this is to use OFFSET function described here: How OFFSET in DAX will make your life easier – Data – Marc (data-marc.com)
I am not sure if that would be the optimal solution in the end. So here is another option to consider:

Is there a particular need to use a calculated column? You could for example create this kind of measure:
var _state1 = MAX(Table1[state1])
var _state2 = MAX(Table1[state2])
return

IF(_state2=3,"Finished",
CALCULATE(MAX(Table2[description]),ALL(Table2[state1]),Table2[state1]=_state1)
)

If you need an additional logic for state2 = 2 then use SWITCH + TRUE:

var _state1 = MAX(table1[state1])
var _state2 = MAX(table1[state2])
var _nextstate1 = CALCULATE(MIN(Table2[state1]),ALL(Table2[state1]),Table2[state1]>_state1)
return
SWITCH(TRUE(),
_state2 = 1, CALCULATE(MAX(Table2[description]),ALL(Table2[state1]),Table2[state1]=_state1),
_state2 = 2,CALCULATE(MAX(Table2[description]),ALL(Table2[state1]),Table2[state1]=_nextstate1),
_state2 = 3, "Finished")

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[v-yiruan-msft · Accepted solution]

Hi @joshua1990 ,

You can create a calculated column as below in table 1 to achieve the first condition:

• If State 2 = 1 or 2, then take Description of table 2

Description = 
CALCULATE (
    MAX ( 'Table 2'[Description] ),
    FILTER (
        'Table 2',
        'Table 2'[Article] = 'Table 1'[Article]
            && 'Table 1'[State 2] IN { 1, 2 }
    )
)

And for the second condition, I'm not clear about " table 2 for the next state 1". What's the meaning of that?  Could you please explain more on it base on the current data of table 1 and table 2? Thank you.

• If State 2 = 3, then take the Description if table 2 for the next state 1.
  
  • If the last state was reached, then display "Finished".

Best Regards