Dax Function: FIRSTNONBLANK

Category: Filter Functions

The FIRSTNONBLANK function in Power BI is a DAX (Data Analysis Expressions) function used to return the first value in a column that is not blank, based on a specified sort order. It is often used in conjunction with aggregation functions to derive meaningful insights from data.

Purpose

The FIRSTNONBLANK function:

  • Identifies the first non-blank value in a column.

  • Evaluates the column row by row, skipping blanks, to retrieve the desired result.

  • Supports dynamic calculations in measures and expressions.

Type of Calculations

The function is designed for:

  • Sequential evaluations to find the first meaningful data point.

  • Context-aware operations within the Power BI model.

  • Returning scalar results for use in visuals or aggregations.

Practical Use Cases

  1. Retrieve First Valid Entry: Identify the first valid date, product, or customer entry in a dataset.

  2. Dynamic Titles: Display dynamic report titles based on data context.

  3. Support Measures: Create conditional logic that relies on non-blank data.


FIRSTNONBLANK(<column>, <expression>)</expression></column>
ParameterTypeDescription
columnColumnThe column from which the first non-blank value is retrieved.
expressionExpressionAn additional expression that determines the context or filter for sorting.

How Does FIRSTNONBLANK Dax Works

The FIRSTNONBLANK function operates by:

  1. Scanning the column from top to bottom (based on the applied context or sort order).

  2. Skipping rows with blank (NULL) values.

  3. Returning the first non-blank value found, or BLANK if no non-blank values exist.

Logical Flow:

  • The expression is evaluated for the provided context to apply additional filters.

  • The function respects sorting rules applied to the column.

Example Formula:

First Non-Blank Date = FIRSTNONBLANK(Sales[Date], 1)

This retrieves the first non-blank date in the Sales table.

What Does It Return?

Scalar Value: The first non-blank value in the specified column, evaluated within the provided context.

When Should We Use It?

  • Dynamic Display: Use in reports to dynamically show the first valid value in visuals.

  • Initialization: Establish default or starting values in measures.

  • Context-Specific Calculations: Ensure calculations skip over irrelevant or missing data.

Examples

Basic Usage :


FIRSTNONBLANK(Customers[Customer Name], 1)

Returns the first customer name that is not blank.

Column Usage


First Sale Amount = FIRSTNONBLANK(Sales[Amount], 1)

Retrieves the first non-blank sales amount.

Advanced Usage


First Valid Product =
FIRSTNONBLANK(
Products[Product Name],
CALCULATE(SUM(Sales[Amount]))
)

Returns the first non-blank product name, considering only products with a sales amount.

Tips and Tricks

  • Combine with Filters: Use with CALCULATE or FILTER for targeted results.

  • Default Handling: Pair with IF to handle blank outputs.

  • Sort Order: Ensure correct sorting in your data model for reliable results.

Performance Impact of FIRSTNONBLANK DAX Function:

  • Dataset Size: Efficient for small to medium datasets; consider indexing for larger datasets.

  • Context Awareness: Performance may vary depending on the complexity of applied filters or sorting.

Related Functions You Might Need

  • LASTNONBLANK: Returns the last non-blank value in a column.

  • FIRSTDATE: Retrieves the first date in a column.

  • BLANK: Used to handle or generate blank values.

  • CALCULATE: Alters filter context dynamically.

Want to Learn More?
For more information, check out the official Microsoft documentation for FIRSTNONBLANK You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the FIRSTNONBLANK function do?

It retrieves the first non-blank value from a column, based on the specified context.

2. How is the sorting handled in FIRSTNONBLANK?

Sorting is determined by the current context or applied sort order.

3. Can FIRSTNONBLANK be used with measures?

Yes, it works seamlessly with measures to provide dynamic results.

4. What happens if all values in the column are blank?

The function returns BLANK if no non-blank values exist.

5. Is FIRSTNONBLANK suitable for large datasets?

It is efficient for typical datasets but may require optimization for very large datasets.

Previous article
Dax Function: FILTER
Next article
Dax Function: FIRSTNONBLANKVALUE