Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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
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
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)
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
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
I attach the complete picture
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |