Understanding FIRSTNONBLANK in Power BI: A Guide for Beginners

The FIRSTNONBLANK DAX function is a powerful tool for retrieving specific information from your data. It finds the first non-blank value in a column, ordered by a specified expression. Think of it like this: you’re looking through a list of items, but you want to find the very first one based on a specific rule, not just the first one you happen to see.

It’s particularly useful for scenarios where you need to get an attribute from the first entry of a filtered set of data, such as the start date of a project, the first status update, or the initial value recorded in a log.

How it Works

The FIRSTNONBLANK function has the following syntax:

FIRSTNONBLANK(<column>, <expression>)

• <column>: This is the column that the function will return a value from. It’s the column you want to get the “first” value of.
• <expression>: This is the expression used for ordering the rows. The function will evaluate this expression for each row, and the result will determine the order. The value from <column> is then returned from the first row where <expression> is not blank. The sorting is always in ascending order.

A key thing to remember is that FIRSTNONBLANK is an iterator function. This means it evaluates the expression row by row, which can be computationally intensive on very large tables.

Example 1: Finding the First Order Date

Imagine you have a sales table and want to find the very first order date for each customer. You could use FIRSTNONBLANK to achieve this.

Let’s assume your Sales table has these columns: [CustomerName], [OrderDate], and [OrderID].

To find the first order date for a customer, you can create a new measure.

DAX Measure:

First Order Date =
FIRSTNONBLANK(
    'Sales'[OrderDate],
    'Sales'[OrderDate]
)

In this example:

• The <column> is 'Sales'[OrderDate]. This is the value we want to return.
• The <expression> is also 'Sales'[OrderDate]. The function will iterate through all the order dates for a given customer, order them chronologically (ascending), and return the first one that isn’t blank.

When you place this measure in a table visual with [CustomerName], you’ll see the earliest order date for each customer. This gives you valuable insight into when your relationship with a customer began.

Example 2: Retrieving the First Status Update

Let’s say you’re tracking a project and have a table with project updates. It includes columns like [ProjectID], [UpdateDate], and [Status]. You want to find the initial status of each project.

You can use FIRSTNONBLANK to get the status from the earliest update date.

DAX Measure:

Initial Status =
FIRSTNONBLANK(
    'Project Updates'[Status],
    'Project Updates'[UpdateDate]
)

In this case:

• The <column> is 'Project Updates'[Status]. This is the value we want to retrieve.
• The <expression> is 'Project Updates'[UpdateDate]. The function will sort all the updates for a given project by their date (ascending) and then return the Status from the first row in that sorted list.

This measure will give you a clear overview of the status a project had when it was first recorded.

How is this different from an Excel LOOKUP?

If you’re coming from Excel, you might be thinking this sounds a lot like a VLOOKUP or XLOOKUP. While FIRSTNONBLANK helps you find a specific value, its approach is quite different and much more flexible.

• Explicit Ordering: FIRSTNONBLANK explicitly separates the column you’re sorting by (<expression>) from the column you’re returning a value from (<column>). In Excel, functions like VLOOKUP are fixed to look in the first column of a range, which can be restrictive. FIRSTNONBLANK gives you the power to sort your data based on one column (like a [LogTimestamp]) and return a value from a completely different one (like [CarrierName]).
• Context and Iteration: FIRSTNONBLANK is designed to work within the filter context of a measure. When used in a visual, it automatically iterates through the rows that are currently being filtered (e.g., all the sales for a specific [CustomerName]), finds the first non-blank value, and returns it. This dynamic behavior is a core strength of DAX that you can’t easily replicate with a single Excel formula.

Think of it less as a simple cell-by-cell lookup and more as a dynamic query that first sorts your data and then picks the first relevant piece of information.

Best Use Case: Getting a Value Across a Many-to-Many Relationship

The true power of FIRSTNONBLANK shines when you combine it with other DAX functions, especially to handle complex relationships. Here’s a perfect example of a real-world problem you might encounter.

Imagine you have two tables:

• NewProjects: Contains a list of new projects and their clients. It has a column [ClientName].
• HistoricalProjects: Contains a record of past projects, the client for each, and the person assigned. It has columns [ClientName] and [Assignee].

Your goal is to populate a new column in the NewProjects table with the name of a person who has previously worked with that client. This is a many-to-many scenario because a client can have many projects, and a person can be assigned to many projects.

To solve this, we can use a combination of CALCULATE, FIRSTNONBLANK, and FILTER to get the correct result.

DAX Calculated Column:

Preferred Assignee =
CALCULATE (
    FIRSTNONBLANK (
        'HistoricalProjects'[Assignee],
        1
    ),
    FILTER (
        'HistoricalProjects',
        'HistoricalProjects'[ClientName] = EARLIER('NewProjects'[ClientName])
    )
)

How it Works:

1. CALCULATE: This is the most powerful function in DAX, and here, it’s modifying the filter context.
2. FILTER: This part creates a new, temporary filter. It’s comparing the [ClientName] in the HistoricalProjects table with the [ClientName] of the current row being evaluated in the NewProjects table (using the EARLIER function).
3. FIRSTNONBLANK: With the new filter applied, this function finds the first non-blank value in the [Assignee] column. The second argument, 1, is a common DAX trick that evaluates to a non-blank value, so FIRSTNONBLANK simply returns the first [Assignee] name it finds within the filtered rows.

By combining these functions, we are telling Power BI: “For each new project, go look at all the historical projects for the same client, and give me the first person’s name you find who was assigned.” This provides a robust and automated way to suggest a preferred assignee based on past experience.

When to use FIRSTNONBLANK?

FIRSTNONBLANK is your go-to function when you need to:

• Find the start of a sequence: Use it with a date or timestamp column to find the first recorded date or event.
• Identify an initial attribute: Get the first value of a category or status that was recorded.
• Handle sparse data: When a column might have many blank values, FIRSTNONBLANK can reliably find the first entry that actually has data.

The function is powerful because it allows you to define the order of iteration independently of the value you’re retrieving, giving you precise control over which “first” value you want to find.

Here is tutorial with example: