backfill data by group - dax question

[sroy]

In powerpivot, I have a table have a flag by group only when the status is complete. There is only one flag per group currently. I want to back fill the flag for the whole group for each status. Below is a small example of the table that I have and the table that I want. How would i write this with dax?

here is the table that I have:

Group   Status                Flag
A           Pending 

A           Pending

A           Complete          Y

B           Started

B           Pending

B           Complete          B

C          Pending

C          Complete      

D          Complete         Y

E          Pending 

F          Complete

G         Started 

G         Complete         B      

here is the table I want

Group   Status                Flag
A           Pending            Y

A           Pending            Y

A           Complete         Y

B           Started             B

B           Pending           B

B           Complete         B

C          Pending

C          Complete

D          Complete         B

E           Pending

F           Complete

G          Started             B

G          Complete        B

[Zubair_Muhammad · Accepted solution]

HI @sroy

Try adding this calculated column

New Column =
CALCULATE (
    FIRSTNONBLANK ( Table1[Flag], 1 ),
    FILTER ( ALLEXCEPT ( Table1, Table1[Group] ), Table1[Status] = "Complete" )
)

[sroy]

Hi @Zubair_Muhammad This works! What if the label wasnt always in the complete row?

[Zubair_Muhammad]

@sroy

In that case, simply

New Column =
CALCULATE (
    FIRSTNONBLANK ( Table1[Flag], 1 ),
    ALLEXCEPT ( Table1, Table1[Group] )
)

[sroy]

Thank you!!