How can I get a column duplicated based on a key

[EaglesTony]

Hi,

  I have this table:

Key          Status            Date

1              Review          null

1              In Progress    8/1/2024

1              Done             null

What I need is to have it use the In Progress status to get the date on related records, so it would look like this:

Key          Status            Date

1              Review          8/1/2024

1              In Progress    8/1/2024

1              Done             8/1/2024

[Anonymous]

Thanks for MNedix's concern about this issue. 

Hi, @EaglesTony 

I am glad to help you.

You can create a calculated column by referring to my DAX formula:

FinalDate = 
VAR InProgressDate = 
    CALCULATE(
        MAX('Table'[Date]),
        FILTER('Table', 'Table'[Key] = EARLIER('Table'[Key]) && 'Table'[Status] = "In Progress")
    )
RETURN
IF(
    ISBLANK('Table'[Date]),
    InProgressDate,
    'Table'[Date]
)

Here are the results:

I have attached the PBIX file of this simple example below, I hope it will be helpful to you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EaglesTony]

Is there a way to do this in a Power Query instead of DAX ?...I do some merges along the way in Power Query, so that is why.

[EaglesTony]

It doesn't like this line:

 EARLIER('Table'[Key]) && 'Table'[Status] = "In Progress")

It is saying "Parameter is not the correct type".

[MNedix]

@EaglesTony did you have a chance to trymy solution or you did try it and it didn't work?

[EaglesTony]

No,  because "

This should cover multiple scenarios where the date could be present in any of the 3 statuses." isn't what I want, just the "In Progress" status to be taken into account, if there is not "In Progress" (based on the Key), then it would be blank.

[MNedix · Accepted solution]

I "brute-forced" it a bit, I hope it will work for you.

IF(
   ISBLANK(
    CALCULATE(
        SELECTEDVALUE (Table1[Date]),
        ALLEXCEPT(Table1,Table1[Key]),Table1[Status]="InProgress")),BLANK(),
        CALCULATE(
        SELECTEDVALUE (Table1[Date]),
        ALLEXCEPT(Table1,Table1[Key]),NOT (ISBLANK(Table1[Date])),Table1[Status]="InProgress"))

[EaglesTony]

Thanks this worked!

[MNedix]

Hey,

Try this:

New date = 
IF(ISBLANK(Table1[Date]),
    CALCULATE (
        SELECTEDVALUE ( Table1[Date] ),
        FILTER ( Table1, NOT ( ISBLANK (Table1[Date] )),ALLEXCEPT(Table1,Table1[Key])) 
), Table1[Date])

This should cover multiple scenarios where the date could be present in any of the 3 statuses.

[EaglesTony]

Not sure if this is a correct approach, but I duplicated the table to only filter in "In Progress", then merged it back to the original table based on the Key, so now all the like keys have the same value.