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
Anonymous
Not applicable

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 

4 REPLIES 4
v-eqin-msft
Community Support
Community Support

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

v-eqin-msft
Community Support
Community Support

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

 

 

11.17.3.1.PNG

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

@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
Not applicable

Let me try to make the issue little clearer

The lookup field is PO_REF

rkbgatech_0-1605281882428.png

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

rkbgatech_1-1605282024027.png

Does this make the question clearer?

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.