FIRSTNONBLANK

How to Use the Power BI DAX function FIRSTNONBLANK

What is the FIRSTNONBLANK function?

The FIRSTNONBLANK function in Power BI returns the first non-blank value in a column or table for a given expression. This function can be used to find the first date, number, or text value in a column or table. The syntax for the FIRSTNONBLANK function is:

FIRSTNONBLANK(❰column❱, ❰expression❱)

Where:

- `❰column❱` is the name of the column or table to search for the first non-blank value.

- `❰expression❱` is the expression to evaluate for each row in the column or table.

How to use the FIRSTNONBLANK function

To use the FIRSTNONBLANK function in Power BI, you must first create a new measure and then use the DAX formula editor to enter the function. Here's how to do it:

1. Open the Power BI Desktop application and open the report where you want to use the FIRSTNONBLANK function.

2. Select the "New Measure" option from the "Modeling" tab in the ribbon.

3. Enter a name for the measure, such as "First Non-Blank Value".

4. In the formula bar, enter the following formula:

First Non-Blank Value = FIRSTNONBLANK(❰column❱, ❰expression❱)

Replace `❰column❱` with the name of the column or table you want to search, and `❰expression❱` with the expression you want to evaluate. For example, if you want to find the first non-blank date in the "Date" column, you would enter the following formula:

First Non-Blank Date = FIRSTNONBLANK(‘Table'[Date], ‘Table'[Sales])

This formula searches the "Date" column in the "Table" table for the first non-blank value where the sales value is not blank.

5. Press Enter to create the measure.

6. Drag and drop the measure onto a visual or table to display the result.

Best practices for using the FIRSTNONBLANK function

Here are some best practices for using the FIRSTNONBLANK function in Power BI:

1. Use the function sparingly - the FIRSTNONBLANK function can be resource-intensive, especially when used on large datasets, so it's best to use it only when necessary.

2. Combine with other functions - the FIRSTNONBLANK function can be combined with other DAX functions, such as FILTER and CALCULATE, to create more complex calculations.

3. Test your formulas - always test your formulas to ensure they are returning the expected results. Use the "New Measure" feature to create a test measure and compare the results with your expected output before using the formula in a report.

The FIRSTNONBLANK function is a powerful tool for finding the first non-blank value in a column or table in Power BI. By following the steps outlined in this article, you can easily create a measure using the FIRSTNONBLANK function and display the results in a visual or table. Remember to use the function sparingly, combine it with other functions, and always test your formulas to ensure they are working correctly.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)