Conditional Look Up / Search Date in other table

[Kees]

Hi,

I have tried several functions, lookupvalue, Max, Maxx and other options suggeted in this forum, but I seem not be able to find the right solution. 

In regard to the below screenshot:

I need in an other POWER BI Table/ Column ( WO[LAST_,PROD DEL DATE]  )  the CO[CO_PROD_DELIVERY_DATE], 

where the CO[CO_ESTIMATE_STATUS] not equal to "Cancelled"

Search Criteria from the other column is: WO[WO_NUMBER]

FYI the CO[CO_ESTIMATE_ID] is a uninque number.

This Table is CO

So I figured to search on WO[WO_NUMBER] in the CO[WO_NUMBER] and then via the highest CO[CO_ESTIMATE_ID] number selecting the CO_PROD_DATE I need.

So thought to find of conditional LOOKUPVALUE, but I cant get it working, probably that I'm just exploring PBI.

Any sugestions? 

Brgds Kees

[Kees · Accepted solution]

Found via coworker the soution.

Create a Measure, content;

Last CO Prod Del Date = Calculate(

    LASTDATE(CO[CO Prod Delivery Date]),

    KEEPFILTERS(WO)

)

That did the trick

[Hariharan_R]

Hi @Kees 

Try the below

Search Date = 
var _Agg = SUMMARIZE(TestTable, TestTable[WONumber], "CO", MAX(TestTable[CONumber]), "ES", MAX(TestTable[EstimateID]), "DATE", max(TestTable[Date]))
RETURN
MAXX(_Agg, [DATE])

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin

[Kees]

Could not get it working like this.