The LASTNONBLANKVALUE function returns the last value resulting from evaluating an expression that is not a Blank, applying this expression to a table ordered according to the values of a certain column.
LASTNONBLANKVALUE(
column,
expression
)
- column: Column whose values determine the order of the table.
- expression: Expression evaluated for each row of the table.
The LASTNONBLANKVALUE function returns a table containing a single column with a single value corresponding to the last value returned by the expression that is not a Blank.
The column can be a reference to a column or a table containing a single column.
The indicated column is added to the filter context for evaluation of the expression. That is, the expression will be evaluated in the filter context created by each value in the column.
Before evaluating the expression, the table is sorted based on the values in the specified column.
To see how the LASTNONBLANK and LASTNONBLANKVALUE functions work, we start from the following table:
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:
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.
