Power Pivot Principles: Introducing the Function FIRSTNONBLANK

Welcome back to the Power Pivot Principles blog.  This week, we are going to learn a new DAX function, FIRSTNONBLANK.

The DAX function FIRSTNONBLANK returns the first value in the column filtered by the current context, where the expression is not blank.  It is a useful function to extract the first non-blank value and can be used as a filter for other calculations.

It has following syntax to operate:

where:

·         column represents a column expression

·         expression is an expression evaluated for blanks for each value of column.

Let’s look at one simple example.  Suppose we have a following data table:

After we have loaded the table into Power Pivot and created a PivotTable, we can populate it with some measures:

This DAX expression calculates the total sales.

We export the result into a PivotTable and the result would be:

We can see that the measure is in fact returning the category two (2), which is what we would expect.

Now, what if we want a measure that will retrieve the value of the first sale? Following the logic of the previous measure we can write:

The result would be:

We can see that the sales amount returning five (5), which is correct according to the data table.

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.