Forum Discussion

BQH15's avatar
BQH15
Regular Visitor
4 years ago
Solved

How to calculate current step by checking previous step status?

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...
Need Help
  • ManguilibeKAO's avatar
    ManguilibeKAO
    4 years ago

    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

ManguilibeKAO's avatar
ManguilibeKAO
Icon for Resolver I rankResolver I
4 years ago

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's avatar
BQH15
Regular Visitor
4 years ago

This worked perfectly, thank you!