Dax Function: LASTNONBLANKVALUE
Category: Filter Functions
The LASTNONBLANKVALUE function in Power BI is a DAX function that evaluates an expression over a table or column and returns the value of the expression for the last row where the column is not blank. It combines the functionalities of LASTNONBLANK with the evaluation of a specified expression.
Purpose
Evaluate meaningful data: Designed to retrieve the last value in a dataset where both the column is not blank and an expression evaluates successfully.
Dynamic context evaluations: Adapts to filter context changes and dynamically evaluates expressions for non-blank rows.
Type of Calculations
Conditional Evaluations: Performs logical and arithmetic evaluations over non-blank rows.
Dynamic Result Calculation: Combines filtering logic with custom calculations.
Practical Use Cases
Ending Balances: Compute the final value of a metric like inventory balance or account balance.
Time-Based Metrics: Identify the latest value for time-sensitive data, such as the last sales amount.
Dynamic Dashboards: Provide up-to-date insights into the latest available data.
LASTNONBLANKVALUE(<column>, <expression>)</expression></column>
| Parameter | Type | Description |
|---|---|---|
Column | Column | The column in which to find the last non-blank value. |
Expression | Expression | A DAX expression to evaluate for each non-blank row in the column. It determines the value to return. |
How Does LASTNONBLANKVALUE Dax Works
Identify Non-Blank Rows: The function scans the column to locate rows that are not blank.
Expression Evaluation: Evaluates the expression for each non-blank row.
Return Value from Last Row: Retrieves the expression result for the last applicable row.
What Does It Return?
Scalar Value: Returns the value of the provided expression for the last non-blank row in the column, considering the current filter context.
When Should We Use It?
To extract the last valid result of an expression in time-sensitive or sequential data.
As part of measures for cumulative reporting where the latest calculated value is required.
In dashboards to dynamically display the most recent non-blank result of a computation.
Examples
Basic Usage :
Last Sale Amount =
LASTNONBLANKVALUE(Sales[Date], SUM(Sales[Amount]))
Returns the sum of the sales amount for the most recent date in the Sales table.
Column Usage
Latest Customer Count =
LASTNONBLANKVALUE(Sales[Customer ID], COUNT(Sales[Customer ID]))
Counts the number of customers for the last non-blank entry in the Customer ID column.
Advanced Usage
Ending Inventory =
CALCULATE(
SUM(Inventory[Stock]),
LASTNONBLANKVALUE(Inventory[Date], SUM(Inventory[Stock]))
)
Calculates the ending stock balance for the most recent date with available inventory data.
Tips and Tricks
Combine with CALCULATE: Use in conjunction with
CALCULATEfor enhanced filter control.Avoid Overloading: Ensure the dataset is optimized for this function by reducing the number of blank entries.
Test Context: Ensure proper filter context to avoid unexpected results.
Performance Impact of LASTNONBLANKVALUE DAX Function:
Efficiency: Best used in datasets with manageable blank values.
Indexing: Proper indexing of columns can enhance performance for large datasets.
Related Functions You Might Need
LASTNONBLANK: Similar but returns the value of the column itself instead of an expression evaluation.
FIRSTNONBLANKVALUE: Evaluates the expression for the first non-blank row.
MAXX: Useful for finding the maximum value within a calculated column.
Want to Learn More?
For more information, check out the official Microsoft documentation for LASTNONBLANKVALUE 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.
It evaluates an expression for the last non-blank row in a column, returning the result of that expression.
No, the function requires an expression to evaluate and return a value.
LASTNONBLANK returns the column value for the last non-blank row, while LASTNONBLANKVALUE evaluates and returns the result of an expression.
Yes, but it is recommended to optimize datasets by minimizing blank values to improve performance.
Yes, it works well with calculated columns and measures.