FIRSTNONBLANK to generate a column

[alfon650]

hello,

I must say that I am a beguinner to DAX, and so you might find this question simple . . .

I am quickly learning all the DAX functions, but I got stucked with Firstnonblank. I have written this formula (see printscreen), in order to go to the simplest behaviour of the formula, but I would have expected the first (smallest) value of the column repeated in all the rows  . . . instead this is what I get. Your help is welcome,

Alfonso (Barcelona)

 

[v-jiascu-msft]

Hi @alfon650,

Please refer to the snapshot below. Yours could be this one.

First NON blank = calculate(firstnonblank(append1[ingresos.2];1), all('table'))

Best Regards,
Dale

[alfon650]

Hi Dale,

thanks for your kind help, but my question is more to do with why my DAX formula doesn't provide what is expected.

I have tried your suggestion and it works, in fact I had previously nested a Firstnonblank inside a Calculate, and it works well. I have googled about it, and the authors never use Firstnonblank alone. 

So the question is why this performance? could it be used on its own? this is what I need to understand its behaviour.

thanks for your support,

Alfonso (Barcelona)

[v-jiascu-msft · Accepted solution]

Hi Alfonso,

That's a trick done by the context. Please refer to dax/firstnonblank-function-dax. The column will be filtered by the context. If it's like this, the column is filtered by row context.

Column = FIRSTNONBLANK(Table1[Column2], 1)

If it's like this, the column is filtered by filter context. 

Column = calculate(FIRSTNONBLANK(Table1[Column2], 1))

So we need to remove the context like below. 

First NON blank = calculate(firstnonblank(append1[ingresos.2];1), all('table'))

Or,

Column = FIRSTNONBLANK(all(Table1[Column2]), 1)

Best Regards,
Dale

[alfon650]

Thanks a lot Dale,

because I had spent a lot of time, due to the description of Microsoft doesn't say that it is affected by the context. In any case I find it misleading that a formula intended to give the Fist value of a column in fact needs some rework to give that result. 

you have helped me a lot ! thanks !!

Alfonso

[alfon650]

I attach the complete picture