Missing data in Firstnonblank

smurfette29 — Thu, 01 Jul 2021 15:06:09 GMT

Hello, I am new to this site and also a beginner PBI user.

I have a problem using Lookupvalue and Firtnonblank, I hope someone can help me figure out.

I used SubDiv = Calculate(Firstnonblank(Ewswb(Subdiv),1),
Filter(Ewswb, Ewswb(Diesicom)=Ref100(Diesicom))

Out of 6700 products to lookup, there are 700 products which are empty/blanks. I double check my source data, and confirmed all informations are available. I asked my friend for help but he also cannot figure it out why some of the rows are blank and some has data.

I hope someone can help me, as I am stressed out already figuring what is the problem.

TIA.

Re: Missing data in Firstnonblank

edhans — Thu, 01 Jul 2021 15:22:15 GMT

I'm not sure exactly, but the code above isn't valid at all. You have no columns. Paste your code in DAX Formatter by SQLBI and copy the results back here.

But the FIRSTNONBLANK() function, what is the ,1 doing? You cannot evaluate 1 and get anything but 1. FIRSTNONBLANK() should be:

FIRSTNONBLANK(Table[Field], [Measure or Expression]), so

First Date = FIRSTNONBLANK( 'Date'[Date], CALCULATE( SUMX( Sales, Sales[Price] * Sales[Quantity] ) ) )

Will return the first date in your model for which there are sales.

topic Missing data in Firstnonblank in DAX Commands and Tips

Missing data in Firstnonblank

Re: Missing data in Firstnonblank