FIRSTNONBLANKVALUE

  • 2 minutes to read

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