Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Brett007
Helper III
Helper III

Lookup value with partial text and Latest numerical result

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 


6 REPLIES 6
v-angzheng-msft
Community Support
Community Support

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
Super User
Super User

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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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
Super User
Super User

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"?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I updated my post with example data

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.