# Vlookup the first non blank value ( not number) with DAX?



## Allamay (Jun 24, 2014)

Dear all, I have a question about DAX. I Googled all over,didn't have the answer, and u guys are my last hope.


Here is two sets of data.


Set 1 order level pull 
Load number   Order number  status 
123                            1               Hot
123                            2               
123                            3
234                            1               Delay
234                            2
345                            1               Expedited
345                            2              
345                            3


So in the load level data set below, if any one order has a status, then this load needs to show status. In the old vlookup, u can directly pull the first non blank row in order level status. But tried lookupvalue in dax, I cannot make it work. This is not a number, so I cannot use the calculate, can u please help to see weather there is a dax formula I can try? Thank you!!!

Load number     Status
123                       ?
234.                      ?
345.                      ?


----------



## sakthikumar (Jun 25, 2014)

could you explain with your data sheet????


----------



## scottsen (Jun 25, 2014)

I suspect FIRSTNONBLANK will help you:  FIRSTNONBLANK Function

Something like =FIRSTNONBLANK(OrderTable[Status], OrderTable[Status])


----------



## XLBob (Jun 25, 2014)

If your load level data in table 1 and order level data in Table2 and they are related on load number column, you can use below to fetch status from table2.
=CALCULATE(VALUES(Table2[status]),FILTER(RELATEDTABLE(Table2),NOT(ISBLANK(Table2[status]))))


----------



## scottsen (Jun 25, 2014)

There is probably a question floating around here about "can multiple order numbers have a non-blank status".  If so, my solution only shows the first, and Bob's solution just blows up


----------



## HillDragon (Jun 25, 2014)

Is he trying to pull a text field to a PivotTable? Because if he is, this will be fun :D


----------



## Allamay (Jun 25, 2014)

Firstly, thank you all for looking into it! 
Secondly, one order might be super hot, so the whole shipment has to be be fagged as hot (imagine a lot orders on a truck).
Thirdly, I m to dump the data to excel and attach here ( freshman on this forum, can I attach excel here?)


----------



## scottsen (Jun 25, 2014)

I always tell people just to use google drive or drop box, but I dunno what the smart people in the main forum us 

(Though, I still like my previous answer... )


----------

