Array Formula - Please Help

[mrguan007]

Hi everyone, 

I am trying to create an array formula in DAX that I can't seem to wrap my mind around. 

Here is what I currently have in excel - which I will explain the logic of what I am trying to do. 

	A	B	C	D
1	Control	Phase	Status	Status Adjusted
2	1.1-C-1	Interim I	Complete	In Progess
3	1.1-C-1	Interim II	In Progess	In Progess
4	1.1-C-2	Interim I	Complete	In Progress
5	1.1-C-3	Interim I	Complete	Complete
6	1.1-C-3	Interim II	Complete	Complete

{=IFERROR(VLOOKUP(A2,IF($B$1:$B$6="Interim II",$A$1:$C$6,""),3,FALSE),"In Progress")}

The meat of the formula is in the red. I am currently running a Array Formula to achieve this in excel. 

So essentially, what I am trying to do is for the same control (i.e. 1.1-C-1), if the Interim II status isn't complete, or if the interim II status doesn't exist for a control, then that control should show as "in progress" for all instances. Once Interim II is completed, then both instances of the control will show as "Complete".

Please let me know! 

[Zubair_Muhammad · Accepted solution]

@mrguan007

Try with following column

Column =
IF (
    CONTAINS (
        FILTER ( Table1, [Control] = EARLIER ( [Control] ) && [Phase] = "Interim II" ),
        [Status], "Complete"
    ),
    "Complete",
    "In Progress"
)

or this one

Column 2 =
IF (
    CONTAINS (
        FILTER ( Table1, [Control] = EARLIER ( [Control] ) ),
        [Status], "Complete",
        [Phase], "Interim II"
    ),
    "Complete",
    "In Progress"
)

[mrguan007]

@Zubair_Muhammad, thank you!!!!