Forum Discussion
Community ChampionDoubts with FIRSTNONBLANK
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
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 :smileytongue:) 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!
6 Replies
v-jiascu-msftMicrosoft Employee
- AlB
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
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 :smileytongue:) 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!
