cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User III
Super User III

Doubts 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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Doubts with FIRSTNONBLANK

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 :

First and Last Query.png

 

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..

PQ Min Max.png

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?

Adding Context.png

 

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

Context Results.png

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 Smiley Tongue) 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!

 

View solution in original post

6 REPLIES 6
Microsoft v-jiascu-msft
Microsoft

Re: Doubts with FIRSTNONBLANK

Hi @AlB,

 

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

Doubts-with-FIRSTNONBLANK

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User III
Super User III

Re: Doubts with FIRSTNONBLANK

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

Super User I
Super User I

Re: Doubts with FIRSTNONBLANK

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 :

First and Last Query.png

 

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..

PQ Min Max.png

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?

Adding Context.png

 

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

Context Results.png

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

 

Super User I
Super User I

Re: Doubts with FIRSTNONBLANK

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 :

First and Last Query.png

 

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..

PQ Min Max.png

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?

Adding Context.png

 

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

Context Results.png

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 Smiley Tongue) 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!

 

View solution in original post

Super User III
Super User III

Re: Doubts with FIRSTNONBLANK

Hi @Nick_M

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! Smiley Very Happy Thank you.

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

 

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

Super User I
Super User I

Re: Doubts with FIRSTNONBLANK

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.  

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors