Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

FIRSTNONBLANK not working as expected

Hi, 

I have 2 tables 

  • PURCHASE_ORDER_LINE
  • DELIVERY_LINE

Both the tables have 2 fields 

  • PO_REF
  • PLANNED_SHIP_DATE

DELIVERY_LINE table has both the fields populated. PURCHASE_ORDER_LINE has only 1 field i.e. PO_REF populated. 

Objective: To obtain the PLANNED_SHIP_DATE from DELIVERY_LINE table and populate it in the PURCHASE_ORDER_LINE table. 

DAX code used: 

PLANNED_SHIP_DATE =
VAR _po_ref =
TRIM ( [PO_REF] )
VAR _result =
CALCULATE (
FIRSTNONBLANK ( delivery_line[PLANNED_SHIP_DATE], 1 ),
FILTER ( ALL ( delivery_line ), TRIM ( delivery_line[PO_REF] ) = _po_ref )
)
RETURN
_result

  

Except for a very few PO_REF all the rows for PLANNED_SHIP_DATE in PURCHASE_ORDER_LINE table is coming out as blank. When I copy the PO_REF (the look up field) in excel and try to match with DELIVERY_LINE table PO_REF field content, I get a 100% match.  Not sure what is happening. 

Kindly help.

Regards,
Rohit 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example?
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
    Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Let me try to make the issue little clearer

      The lookup field is PO_REF

      The table used as reference is DELIVERY_LINE, which does have the PLANNED_SHIP_DATE and the PO_REF fields populated. 

      Does this make the question clearer?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Not very clear...Did you want to get the matched Date from DELIVERY_LINE table?

    I tried to create a calculated column using the formula you provided, and I also use LOOKUPVALUE() function to do it.

     

    Column =
LOOKUPVALUE (
    'DELIVERY_LINE'[PlANNED_SHIP_DATE],
    'DELIVERY_LINE'[PO_REF], 'PURCHASE_ORDER_LINE'[PO_REF]
)

     

     

    Here is the pbix file.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,
    Eyelyn Qin

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Sorry to disturb you...

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution,so that more people will benefit from it. 

     

    Best Regards,
    Eyelyn Qin