Updating the Status of different Steps for a particular request based on a particular value

[Luzadriana255]

Hello

I have a 5 step process for different requests (A, B and C in the table). If one step for a request have the value "Cancelled" (e.g. Step 2 for request A and Step 3 for request C), then all the status for all the steps of that particular request need to be updated to "Cancelled" in a new column (Updated status). If no step for a request have the status cancelled (e.g. Request B) then the status will remain the same for all the steps of that request in the Status column. Could you please help me to find a DAX Formula that can be apply for the "Updated status" column? Thank you!

Request	Step	Status	Updated Status
A	1	Finished	Cancelled
A	2	Cancelled	Cancelled
A	3	Finished	Cancelled
A	4	Pending	Cancelled
A	5	Finished	Cancelled
B	1	Finished	Finished
B	2	Pending	Pending
B	3	Finished	Finished
B	4	Pending	Pending
B	5	Pending	Pending
C	1	Finished	Cancelled
C	2	Finished	Cancelled
C	3	Cancelled	Cancelled
C	4	Pending	Cancelled
C	5	Pending	Cancelled

[Luzadriana255]

Thank you!

[Anonymous · Accepted solution]

Hi  @Luzadriana255 ,

Here are the steps you can follow：

1. Create calculated column.

Updated Status1 =
var _select=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Request]=EARLIER('Table'[Request])),"1",[Status])
return
IF(
    "Cancelled" in _select,"Cancelled",[Status])

2. Result:

Best Regards,

Liu Yang

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