Automatically update a column when a value in another column exist more than once

[rlee1982]

Dear all

I have a log table to record each action ("Action" column), when it happen ("Log_Time" column), and its meaning ("Process" column).

My target is to automatically update the "Process" column when there is a new action happen after the previous action. 

Below is an example:

Action A, B, and C happened again, so the process start over after the latest Action A.

Action	Log_Time	Process
Action A	1/1/2022	Start of Process A
Action B	2/1/2022	Start of Process B
Action C	3/1/2022	Start of Process C
Action D	3/15/2022	Start of Process D
Action E	4/1/2022	Start of Process E
Action A	5/1/2022
Action B	5/4/2022
Action C	5/15/2022

Action	Log_Time	Process
Action A	1/1/2022
Action B	2/1/2022
Action C	3/1/2022
Action D	3/15/2022
Action E	4/1/2022
Action A	5/1/2022	Start of Process A
Action B	5/4/2022	Start of Process B
Action C	5/15/2022	Start of Process C

Many thanks!

[rlee1982]

Thanks Sturla! It works!

[sturlaws]

Hi,

you create this calculated column

which returns this:

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[rlee1982]

Hi Sturla, would you mind sharing the pbix file? Thanks!

[sturlaws · Accepted solution]

sorry, somehow forgot to paste in the code for the calculated column:

Column =
VAR _action_A_max =
    CALCULATE (
        MAX ( 'Table'[Log_time] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Process] = "Action A" )
    )
RETURN
    IF (
        'Table'[Log_time] >= _action_A_max,
        CONCATENATE ( "Start of Process ", RIGHT ( 'Table'[Process], 1 ) ),
        BLANK ()
    )