The FIRSTNONBLANK DAX function is used to returns the first value in the column for which the expression has a non blank value. This function comes under Time Intelligence DAX functions category.
Syntax
FIRSTNONBLANK(<column>,<expression>)
| S no. | Parameter | Description |
| 1 | Column | The source values. |
| 2 | Expression | The expression to be evaluated for each value. |
Let’s start with an example step by step-
Step-1: The dataset is as described below, and its name is FNBDAX.
First Non Blank Dataset
Step-2: Create a measure for Sum of Total Sales.
Tot sales = SUM(FNBDAX[Sales])
Step-3: Now, let’s create a measure to retrieve the first non-blank date based on the sales data.
FNB_Date = FIRSTNONBLANK(FNBDAX[Date],[Tot sales])
Step-4: To view the measure’s output, add a “Card” visual from the Visualization pane and drag the desired measure onto it.
DAX Measure Output
To get the first non blank value, Power BI DAX engine sort the data in backend then returns the value. To verify this, sort the table data in ascending order based on the date column.
After the sorting, you will observe that the first non-blank date is 2018-01-05.
FirstNonBalankDax_1
Step-5: As per above Screen shot, First Non Blank Month based on Salary is JAN. But practically this is not correct , as I previously mentioned, Power BI sorts data based on the column’s value.
If you sort the month column, you will find that FAB is the first non-blank month. Let’s understand-Measure for first non blank month based on salary-
FNB_Month = FIRSTNONBLANK(FNBDAX[Month],[Tot sales])
FirstNONBlank DAX Output
FirstNonBlank DAX example
Similar DAX: LASTNONBLANK
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.