Lookup value with partial text and Latest numerical result

[Brett007]

I have two tables.
I would like to pull latest Milestone and Status from 'Reviews' into the 'allChart' table by program. 

In a table with a matching single program # I would like to return the Latest Milestone with an E in the title so the result would be

E3 Exit

Any ideas would be great. 

This is what I have tried thus far,

Last Exited ER =

VAR Program = allChart[Program Number]

VAR ER = LOOKUPVALUE(Reviews[Review],Reviews[Program Number],allChart[Program Number])

RETURN

MAXX(

Filter(Reviews,Reviews[Program Number] = Program && LEFT(Reviews[Program Number], 1) = "E"

)

,Reviews[Review]

)

It returned all blanks
Excel Data 
PBI File 

[v-angzheng-msft]

Hi, @Brett007 

Not clear what you want to do, is the target table in the PBIX file what you want to get, how do you get the max, min and target? This case doesn't look very difficult to implement, but I can't get a clear idea of what you want to do, just like shooting arrows in the dark.

Could you please consdier sharing more details about it for further discussion?

Best Regards,
Community Support Team _ Zeon Zheng

[TomMartens]

Hey @Brett007 ,

my assumptions are both tables are not related and both tables are called first table and second table, then try this DAX statement to create a calculated column in second table:

status from 1st table =
var __program = 'first table'[Program]
var LatestMilestone = 
    MAXX(
        FILTER ( 'first table'
            , 'first table'[Program] = __programm && LEFT( 'first table'[Program] , 1 ) = "E"
        )
        , 'first'[Milestone]
    )

Hopefully, this returns what you are looking for.

If not create a pbix file that contains sample data, but still reflects your data model (tables, data types of the columns, relationships, calculated columns, and relevant measures) Upload the file to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.

Regards,

Tom

[Brett007]

I was not able to get your formula to work.  I tried to modify it.  I posted the results in the original question above.  Thank you for your assistance.

[Ashish_Mathur]

Hi,

What other possible entries can you have in the Milestone column?  What do you mean by "Latest Milestone with an E in the title"?

[Brett007]

I updated my post with example data

[Ashish_Mathur]

Hi,

Try these calculated column formulas

Latest date with an E = CALCULATE(MAX(Reviews[Date]),FILTER(Reviews,Reviews[Program Number]=EARLIER(allChart[Program Number])&&LEFT(Reviews[Review],1)="E"))

Latest review = LOOKUPVALUE(Reviews[Review],Reviews[Program Number],allChart[Program Number],Reviews[Date],[Latest date with an E])

Latest status = LOOKUPVALUE(Reviews[Status],Reviews[Program Number],allChart[Program Number],Reviews[Date],[Latest date with an E])

Hope this helps.