Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

 

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_TimeProcess
Action A1/1/2022Start of Process A
Action B2/1/2022Start of Process B
Action C3/1/2022Start of Process C
Action D3/15/2022Start of Process D
Action E4/1/2022Start of Process E
Action A5/1/2022 
Action B5/4/2022 
Action C5/15/2022 

 

 

Action

Log_TimeProcess
Action A1/1/2022 
Action B2/1/2022 
Action C3/1/2022 
Action D3/15/2022 
Action E4/1/2022 
Action A5/1/2022Start of Process A
Action B5/4/2022Start of Process B
Action C5/15/2022Start of Process C

 

Many thanks!

1 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 ()
    )

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Thanks Sturla! It works!

sturlaws
Resident Rockstar
Resident Rockstar

Hi,

 

you create this calculated column

which returns this:

sturlaws_0-1654117639312.png

 

Cheers,
Sturla

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

Anonymous
Not applicable

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

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 ()
    )

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.