FIRSTNONBLANK not working as expected

[Anonymous]

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 

[Anonymous]

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

[Anonymous]

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

[amitchandak]

@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]

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?