Doubts with FIRSTNONBLANK

[AlB]

Hi there,

The definition of the function FIRSTNONBLANK(<column>; <expression>)  is "Returns the first value in the column for which the expression has a non blank value".
So in the example:

FIRSTNONBLANK ( 'Date'[Date],
               CALCULATE ( SUM ( Inventory[UnitsBalance] ) )
        )

FIRSTNONBLANK returns the first date where the CALCULATE( ...) is not a blank. So far so good.

Now what I am struggling to understand is the reasoning for:  FIRSTNONBLANK('Date'[Date]; 1)

As you know, this returns the first non-blank date. However here comes my doubt:

The <expression> is always 1 and therefore NEVER blank. So the function in this  case should return always the contents of row number 1, even if it's blank, since the expression is non-blank. Let me give you an example:

Imagine we have a column 'Date'[Date] in which row number 1 is blank and rows 2,3,...,N (i.e. all the other rows) are non-blank. According to my reasoning, the function FIRSTNONBLANK('Date'[Date];1) starts by checking row number 1. It calculates the value for <expression> for this row and the result is 1 (non-blank). So row number 1 is already the first row for which the expression has a non-blank value. Therefore, the function should return the contents of row number 1, i.e. a blank.

However we know FIRSTNONBLANK('Date'[Date];1) will return the contents of row number 2.

What is wrong with my reasoning?
Many thanks

As you know, this returns the first non-blank row in the column 'Date'[Date]. If we follow the reasoning above, though, the <expression> is always 1 and therefore NEVER blank. So the function should always return the first row in the column even if it is blank, as the expression is non-blank. I mean, imagine we have a column in which the first row is blank and the rest aren't. The function starts by checking the first row, it calculates the <expression>, result is 1 (non-blank) and therefore it should return that first row.

What am I understanding wrongly here?

Many thanks

[v-jiascu-msft]

Hi @AlB,

It works as you said. Please download the demo from the attachment.

Best Regards,
Dale

[AlB]

Hi @v-jiascu-msft

Thanks for your reply. I believe you are not addressing the actual question. I've edited my initial post to try and make more clear what my issue is.

Thanks

[Anonymous · Accepted solution]

It's important to remember that the order you see the data in the tables does not really matter.  Everything happens in context.  I used the World Wide Importers to test this out.  So let's look at what happens when we use FIRSTNONBLANK and LASTNONBLANK:

EVALUATE

ROW (
	"First NonBlank",
	FIRSTNONBLANK('Fact Sale (2)'[Profit], 1),
	
	"Last NonBlank",
	LASTNONBLANK('Fact Sale (2)'[Profit],1)
)

So we get a single value for each :

The question becomes, what the hell is that???  We look at  our handy-dandy power query table, as we see that is the min and max of that entire table..

What also becomes clearer at this stage is that it doesn't really matter what the order of the table is when we physically see it. First/LastNonBlank are iterators and will internally sort the table (which is why we should only use these on lookup tables, as you really dont want to be iterating a fact table like I am here, but this is for demonstration only so I guess that's ok).  Basically we have found a different way to find the min and max. of the whole table becomes there is no other context.   

What happens when we add context?

I'd hope that the Min and Max is -24 and 1800 respectively...

Alright, they match! ( good thing else I'd have to delete this post...) It comes down to context, as it more or less always does.  Now, using these to find the min and max of table works, but we have functions just for that.  That is why LASTNONBLANK, FIRSTNONBLANK, ETC are used in conjunction with CALCULATE and measures.  

Something I have come to understand is that theory is great and most definitely needed, but also just seeing how all this stuff places out in the real world is key.  It's like mechanic who can know every in and out of a race car, but never drive it, vs. the race car driver who is in the car everyday and just knows enough about the car.  When the rubber meets the road ( well that was a pleasant unintended pun ) who do you think is the better driver??  And if something isn't working there are so many resources readily available that you can probably find any answer pretty quickly.  Anyhow that is just my .02.  

Hope this helped!

[AlB]

Hi @Anonymous

First of all, and once again, thanks a lot for a superb post and the effort you've put into it. You were so enthusiastic that you even posted the answer twice! Thank you.

Now your very first sentence is pure gold for me, the ultimate revelation (so far) from the Power Query preacher:

It's important to remember that the order you see the data in the tables does not really matter

This has profound implications, as my previous understanding of FIRSTBLANK was based precisely on the assumption that physical order did matter. I posted this question also in SQLBI and Marco Russo mentioned this too. I am now left scratching my head as I have used FIRSTNONBLANK frequently with my erroneous assumption in mind and I've always got it to work. 

In any case, and this probably would be a futile discussion, I believe the description for the function in the documentation is misleading/incomplete at best:  

Returns the first value in the column, <column>, filtered by the current context, where the expression is not blank 

Where does one get from this that the first value is actually the first in the sorted column? Nowhere in my view. I find that the DAX documentation frequently omits important details.

Although you showed something much more important, my initial question remains unanswered, I believe. But since it has to do with the definition I have to review if it's worth asking again. I'll go and let what you said sink in, make the rubber meet the road with some tests and likely be back with some additional comment/question.

Thank you

[Anonymous]

Glad it helped, and no idea why it posted it twice... Anyhow, I wouldn't get to caught up in this. Seems like you have an understanding of it and can deploy it in the real world just fine.  So why worry about something that may or may not have an affect?  In my head there's the theoretical side and then there's the practical side (much like many other professions) and where I want to spend the majority is the practical with a little theory sprinkled it.  Experience is the best teacher in my view.  

[Anonymous]

It's important to remember that the order you see the data in the tables does not really matter.  Everything happens in context.  I used the World Wide Importers to test this out.  So let's look at what happens when we use FIRSTNONBLANK and LASTNONBLANK:

EVALUATE

ROW (
	"First NonBlank",
	FIRSTNONBLANK('Fact Sale (2)'[Profit], 1),
	
	"Last NonBlank",
	LASTNONBLANK('Fact Sale (2)'[Profit],1)
)

So we get a single value for each :

The question becomes, what the hell is that.  We look at  our handy-dandy power query table, as we see that is the min and max of that entire table..

What also becomes clearer at this stage is that it doesn't really matter what the order of the table is when we physically see it. First/LastNonBlank are iterators and will internally sort the table.  Basically we have found a different way to find the min and max. of the whole table becomes there is no other context.   

What happens when we add context?

I'd hope that the Min and Max is -24 and 1800 respectively...

Alright, they match! ( good thing else I'd have to delete this post...) I think it comes down to context, as it more or less always does.  Now, using these to find the min and max of table works, but we have functions just for that.  That is why LASTNONBLANK, FIRSTNONBLANK, ETC