FIRSTNONBLANKVALUE
An expression's first value that is not blank, over a column's values in their order.
Group: Time intelligence. Compatible with DAX.
Syntax
FIRSTNONBLANKVALUE ( <column>, <expression> )| Parameter | Type | Description |
|---|---|---|
column | Column | The column whose values are gone through, in their sort order: dates, numbers or text. A table of one model column, such as VALUES ( Periods[Month] ), works too. |
expression | Expression evaluated for each row | Worked out for each value, with the value as a filter, until it is not blank. |
Returns
Same type as the expression. The expression's first result that is not blank.
Remarks
- The values are those visible in the filter context, smallest first.
- Unlike
FIRSTNONBLANK, the expression is evaluated with each value as a filter, so a plain aggregation such asSUM ( Ledger[Amount] )works withoutCALCULATE. - The column must be one of the model, or a table of one such as
VALUES; a table made in the query is an error, as in Power BI. - When every result is blank, the answer is BLANK.
Examples
The first month's amount, by scenario
EVALUATE
SUMMARIZECOLUMNS (
Ledger[Scenario],
"First month", FIRSTNONBLANK ( Periods[Month], [Booked amount] ),
"Its amount", FIRSTNONBLANKVALUE ( Periods[Month], [Booked amount] )
)
ORDER BY Ledger[Scenario]| Ledger[Scenario] | First month | Its amount |
|---|---|---|
| Actual | 2024-01-01 | 166021861.31 |
| Budget | 2024-01-01 | 170269757.42 |
| Forecast | 2026-01-01 | 178535432.21 |
The first price quoted
EVALUATE
SUMMARIZECOLUMNS (
Prices[Commodity],
"First price", FIRSTNONBLANKVALUE ( Prices[Date], MAX ( Prices[Close] ) )
)
ORDER BY Prices[Commodity]| Prices[Commodity] | First price |
|---|---|
| Aroma hops | 411.65 |
| Malting barley | 2642.72 |
Related functions
LASTNONBLANKVALUE, FIRSTNONBLANK, OPENINGBALANCEMONTH, CALCULATE.