How to calculate current step by checking previous step status?

[BQH15]

Hello,

How would I get Power BI to show the current step of an order number?

Ideally the formula would group order number and look for the first step where status is "In Progress". ( Needs to check step A status first, then b, lastly c)

The yellow column would be the needed result.

[ManguilibeKAO · Accepted solution]

Hi BQH15,

Here's a solution:

a) Create a calculcated column Rank in the table, with the formula:

Rank = SWITCH(TRUE(),
[Step]="a",1,
[Step]="b",2,
[Step]="c",3
)

b) Create a calculated column in the table with the formula:

Current Step =
Var p = 'Feuil1'[Order Number]
Var t = FILTER('Feuil1',[Order Number]=p && [Status]="In Progress")
Var r = minX(t,[Rank])
Var s=FILTER(t,[Rank]=r && [Order Number]=p)
return MAXX(s,[Step])

Note that Feuil1 is the name of the table containing the data.

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

Manguilibe KAO

[BQH15]

This worked perfectly, thank you!