Find the value in column [A] that aligns with explicit values in columns [B], [C, [D]

[bernardclark]

I am trying to set up a flag that I can display to determine the current status of a number of projects. 

My base DB is set up in this way:

• [Project Name] - each project has an ID
• [Section] - Variable groupings for ease of access (ex: Schedule, Execution times, Economics...)
• [DG] - Decision Gate, the milestone when the data was recorded (DG1, FID...). I am interested in the values tagged as 'Actual' here
• [Variable] - the name of the metric being measured/tracked (ex: Date of Decision Gate 1, Date of First Investment, Date of Project Operations starting...)
• [Base]/[Date] - the actual values, split out because numbers and dates don't play nice

I am trying to set up a way that I can tag each Project as the value in [Variable] that is based on the last date value in the [Date] column with "Actual" for [DG], per Project. 

I asked around yesterday an the recommendation was three fold:

1) create a new table based on

Distinct Project Names = DISTINCT(base[Project Name])

2) create a measure in the 'Project Names' table to flag the last date entered, which matches up with the status that I am looking for. This works as intended. 

cm_DGDate = CALCULATE(LASTDATE(base[Date]),base[Project Name] in ALLSELECTED('Project Names'[Project Name]),main[DG] = "Actual")

3) create another measure in the 'Project Names' table to find the variable that aligns with the [cm_DGDate]. This does not work as desired, instead just returning the first unique value in variable sorted alphabetically.

cm_LastDG = CALCULATE(FIRSTNONBLANK(main[Variable],1),main[Project Name] in ALLSELECTED('Project Names'[Project Name]),main[DG] = "Actual",main[Date]>date(2000,1,1))

I've been poking around all morning today and think that the LookUpValue function as a measure should work. But it doesn't. I need help figuring out why or a different method to do this. 

cm_test2 = LOOKUPVALUE(main[Variable],

main[Section],"Schedule",

main[DG],"Actual",

main[Date],[cm_DGDate])

This works fine when I have a filter to only one project. When I add 2+ projects to the filter it returns the error "A table of multiple values was supplied where a single value was expected".

It seems logical to me that I could add in the following arguments:

cm_test2 = LOOKUPVALUE(main[Variable],

main[Section],"Schedule",

main[DG],"Actual",

main[Date],[cm_DGDate],

main[Project Name],'Project Names'[Project Name])

but it will not let me call the key defining column of the table. 

My questions: 

1. Why does LookUpValue not draw on the key column in the table? I thought measures were supposed to call on a unique ID based on the variables in each row of the table? Especially when it has no problem pulling the other calculated column [cm_DGGate]
2. Is there a way to get LookUpValue to pull the Project Name?
3. Is there another function that provides this ability to match the value in column [Variable] that aligns with explicit values in columns [DG], [Project Name], [Section]?

edit: removed amgibuity mentioned below.

[lbendlin]

please clarify if you meant calculated column or measure.  The term "calculated measure" is ambiguous.

[bernardclark]

Measure.

I had it stuck in my mind that they were called calculted columns and calculated measures.