Forum Discussion

Kees's avatar
Kees
Helper I
4 years ago
Solved

Conditional Look Up / Search Date in other table

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

Need Help
Tips and Tricks
  • Kees's avatar
    Kees
    3 years ago

    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

3 Replies

  • Hariharan_R's avatar
    Hariharan_R
    Solution Sage
    4 years ago

    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's avatar
      Kees
      Helper I
      3 years ago

      Could not get it working like this.

  • Kees's avatar
    Kees
    Helper I
    3 years ago

    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