Home > Educational resources > DAX Functions

LASTNONBLANK

The LASTNONBLANK function returns the last value in a column for which an expression does not return a Blank.

Syntax

LASTNONBLANK(
    column,
    expression
)

Parameters
  • column: Column whose values you want to analyze.
  • expression: Expression evaluated for each value in the column.
Returned value

The LASTNONBLANK function returns a table containing a single column with a single value corresponding to the last value of the column for which the indicated expression does not return a Blank.

Additional Information

The column argument can be a reference to an existing column, a table with a single column, or a Boolean expression that defines a table with a single column. In the latter case, the expression cannot refer to a measure, nor use the CALCULATE function nested, nor use iterative (table scan) functions or functions that return a table.

The indicated column is not added to the filter context for expression evaluation. That is, the expression is not going to be evaluated in the filter context created by each value in the column. If we want the context to be propagated, we must do so explicitly by calculating the expression as an argument to CALCULATE.

Before evaluating the expression, the table is sorted based on the values in the specified column.

Examples

To see how the LASTNONBLANK and LASTNONBLANKVALUE functions work, we start from the following table:

Tabla de datos

We can see how the last two columns include empty cells. We read the table from Power BI and create two measures applying both functions using the product of width ("Ancho" column) and height ("Alto" column) as an expression:

LASTNONBLANK = 
    LASTNONBLANK(
        data[Valor],
        CALCULATE(SUM(data[Ancho]) * SUM(data[Alto]))
    )

In the previous expression we enclose the expression in the CALCULATE function so that it is evaluated in the row context created by each value of the indicated column.

LASTNONBLANKVALUE = 
    LASTNONBLANKVALUE(
        data[Valor],
        SUM(data[Ancho]) * SUM(data[Alto])
    )

In this second expression it is not necessary to use the CALCULATE function since the LASTNONBLANKVALUE function already forces the expression included as the second argument to be calculated in the row context of each value in the column.

Now, we bring both measurements to a multi-row card:

Medidas creadas

We clearly see the differences between both functions: LASTNONBLANK returns values from the indicated column. Specifically, it returns the last value for which the expression does not return a Blank value (the 5 corresponding to the penultimate row). It is important to insist that, for the calculation of the function, the table is going to be ordered according to the values of the column. In this example the rows are already ordered according to this criterion, but the result obtained would be different if the order were different.

In contrast, LASTNONBLANKVALUE returns the result of the expression. Specifically, the last non-blank value obtained after going through the table ordered according to the values of the column.

Related functions
Scenarios
Category
Time intelligence
Submitted by admin on Tue, 01/15/2019 - 15:46