Edit

FIRSTNONBLANKVALUE

  • Article

Applies to: Calculated column Calculated table Measure Visual calculation

Note

This function is discouraged for use in visual calculations as it likely returns meaningless results.

Evaluates an expression filtered by the sorted values of a column and returns the first value of the expression that is not blank.

Syntax

FIRSTNONBLANKVALUE(<column>, <expression>)

Parameters

Term Definition
column A column or an expression that returns a single-column table.
expression An expression evaluated for each value of <column>.

Return value

The first non-blank value of <expression> corresponding to the sorted values of <column>.

Remarks

  • The column argument can be any of the following:

    • A reference to any column.
    • A table with a single column.

  • This function is different from FIRSTNONBLANK in that the <column> is added to the filter context for the evaluation of <expression>.

  • This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example

The following DAX query,

EVALUATE 
SUMMARIZECOLUMNS(
  DimProduct[Class],
  "FNBV",
  FIRSTNONBLANKVALUE(
    DimDate[Date],
    SUM(FactInternetSales[SalesAmount])
   )
)

Returns,

DimProduct[Class] [FNBV]
L 699.0982
H 13778.24
M 1000.4375
533.83