Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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" )
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" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |