The FIRSTNONBLANK function returns the first value in a column for which an expression does not return a blank.
FIRSTNONBLANK(
column,
expression
)
- column: Column whose values you want to evaluate.
- expression: Expression evaluated for each value in the column.
The FIRSTNONBLANK function returns a table containing a single column with a single value corresponding to the first value in the column for which the expression does not return a Blank.
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 nested CALCULATE function, 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.
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.
