Power Pivot Principles: The A to Z of DAX Functions – FIRSTNONBLANK

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at FIRSTNONBLANK. 

The FIRSTNONBLANK function

The FIRSTNONBLANK function is one of the time intelligence functions, it returns the first value in the column where the expression has a non-blank value.  It has the following syntax:

• column: this is required and is a column expression.  The column argument can be any of the following:
  • a reference to any column
  • a table with a single column
• expression: this is also required and is an expression evaluated for blanks for each value of column.

It should be noted that:

• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Since the FIRSTNONBLANK function is a time intelligence function, it should be noted that:

• all dates need to be present for the years required.  It is suggested that all the days in this span, from January 1 to December 31, must be included in the Date table.  The date table must include all dates from commencement to the last day of a fiscal year if the report solely refers to fiscal years
• a column with a DateTime or Date data type and unique values is required.  Typically, this column is known as Date.  Although it is common practice, this is not necessary when defining associations with other tables.  However, the ‘mark as Date Table’ feature should relate to the Date column, which must have distinct values
• the Date table must be designated as a date table in the model in case the relationship between the Date table and any other table is not dependent on the Date.

Let’s consider the following example, where we have the Sales table:

Firstly, we will create a simple measure called Sales_Amount to be our expression:

Then, we will create our FIRSTNONBLANK measure to grab the first date we make a sale in the Date column of the Sales table:

We then put it in the ‘Values’ section of the PivotTable Fields pane, viz.

Since in our data we make the first sale on 3^rd January 2020, the measure will display 3/01/2020. 

Let’s consider another example:

This measure identifies the lowest value in the Sales Amount column as the first value, hence it displays 90,000:

Alternatively, we can put zero [0] in the expression argument to get the same result here:

This will produce the following result:

Come back next week 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.