The FIRSTNONBLANKVALUE function returns the first 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.
FIRSTNONBLANKVALUE(
column,
expression
)
- column: Column whose values determine the order of the table.
- expression: Expression to evaluate for each value of the column.
The FIRSTNONBLANKVALUE function returns a table containing a single column with a single value corresponding to the first 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 the 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 FIRSTNONBLANK and FIRSTNONBLANKVALUE functions work, we start from the following table:
We can check how the three columns include empty cells. We read the table from Power BI and create two measures applying both functions using the product of width ("Ancho") and height ("Alto") as an expression:
FIRSTNONBLANK =
FIRSTNONBLANK(
data[Valor],
CALCULATE(SUM(data[Ancho]) * SUM(data[Alto]))
)
In the previous measure 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.
FIRSTNONBLANKVALUE =
FIRSTNONBLANKVALUE(
data[Valor],
SUM(data[Ancho]) * SUM(data[Alto])
)
In this second measure it is not necessary to use the CALCULATE function since the FIRSTNONBLANKVALUE function already forces the expression included as the second argument to be calculated in the row context of each value of the referenced column.
Now, we bring both measures to a multi-row card:
We clearly check the differences between both functions: FIRSTNONBLANK returns values from the indicated column. Specifically, it returns the first value for which the expression does not return a Blank (the 2 corresponding to the second 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 would be different if the values included in the column were different.
In contrast, FIRSTNONBLANKVALUE returns the result of the expression. Specifically, the first value obtained that is not Blank after going through the table ordered according to the values of the column.
