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

[Allamay]

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]

could you explain with your data sheet????

 

[scottsen]

I suspect FIRSTNONBLANK will help you: FIRSTNONBLANK Function

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

 

[XLBob]

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]

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]

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

 

[Allamay]

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]

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