cancel
Showing results for
Did you mean:
Highlighted
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

## 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 :

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!

6 REPLIES 6
Microsoft

## Re: Doubts with FIRSTNONBLANK

Hi @AlB,

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

## Re: Doubts with FIRSTNONBLANK

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

## 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 :

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

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 :

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!

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

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.

Announcements

#### 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!

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?

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

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