In this writing, I want to share how I use LASTNONBLANK and LASTNONBLANKVALUE DAX functions, or how I do not use those to get the same & correct answers, in my Power BI report.
When I began learning DAX, I struggled to understand how to apply LASTNONBLANK and LASTNONBLANKVALUE function properly. When I could not solve it by writing with the those DAX functions, I sometimes wrote a longer DAX formula without using those.
In fact, these two DAX functions triggered me to start to write DAX formula in several different ways and compare results & performance. Many people say try to write DAX in several different ways to compare results and performance. This helps later when a DAX formula shows a performance issue. Writing DAX formula in several different ways is one of the methods that help to solve some of the performance problems. However, writing two or three DAX formulas to get the one-same-result is not common for many people.
I will demonstrate how I author four different DAX formulas to get a one-same-result by using the below sample data, and I will try to compare what approach provides better performance when analyzing data to figure out which date is the last date that has non blank information, and what information is shown on the last non blank date in the selected period.
Datamodel
Sales table
A simple matrix visualization to show the total sales by using a DAX measure, [Sales total:], looks like below.
Here are some questions from a business.
Q1. What date is the last date that has the information (non-blank) on each month?
Q2. What is the last amount (non-blank amount) on the non-blank date on each month?
Q3. Who is the last customer on the non-blank date on each month?
In order to answer Q1, I tried four different versions of the DAX measures, like below.
Before stepping further, check out the explanation of LASTNONBALNK DAX function in the link down below.
And, keep in mind that the first argument in the function must be a column, or a single column table.
Last non blank date V1: = CALCULATE ( LASTNONBLANK ( 'Calendar'[Date], [Sales total:] ), ALL ( 'Calendar' ), VALUES ( 'Calendar'[Month-Year] ) )
Last non blank date V2: = LASTNONBLANK ( SUMMARIZE ( CALCULATETABLE ( 'Calendar', ALL ( 'Calendar' ), VALUES ( 'Calendar'[Month-Year] ) ), 'Calendar'[Date] ), [Sales total:] )
Last non blank date V3: = VAR _monthsales = FILTER ( ADDCOLUMNS ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month-Year] = MAX ( 'Calendar'[Month-Year] ) ), "@sales", [Sales total:] ), NOT ( [@sales] == BLANK () ) ) RETURN MAXX ( _monthsales, 'Calendar'[Date] )
Last non blank date V4: = VAR _monthsales = FILTER ( ADDCOLUMNS ( CALCULATETABLE ( 'Calendar', ALL ( 'Calendar' ), VALUES ( 'Calendar'[Month-Year] ) ), "@sales", [Sales total:] ), NOT ( [@sales] == BLANK () ) ) RETURN MAXX ( _monthsales, 'Calendar'[Date] )
There are more than 4 ways to have the same result. But for now, let's keep it as it is.
As shown in the above, [Last non blank date V3:] shows the slowest performance.
➡️Please keep in mind that analyzing the performance with a small amount of data ( 1,246 rows in the sales table) might lead to an incorrect performance analysis.
In order to answer Q2, I tried four different versions of the DAX measures, like below.
Last non blank value V1: = CALCULATE ( LASTNONBLANKVALUE( 'Calendar'[Date], [Sales total:] ), ALL ( 'Calendar' ), VALUES ( 'Calendar'[Month-Year] ) )
Last non blank value V2: = LASTNONBLANKVALUE( SUMMARIZE ( CALCULATETABLE ( 'Calendar', ALL ( 'Calendar' ), VALUES ( 'Calendar'[Month-Year] ) ), 'Calendar'[Date] ), [Sales total:] )
Last non blank value V3: = VAR _monthsales = FILTER ( ADDCOLUMNS ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month-Year] = MAX ( 'Calendar'[Month-Year] ) ), "@sales", [Sales total:] ), NOT ( [@sales] == BLANK () ) ) RETURN CALCULATE ( [Sales total:], 'Calendar'[Date] = MAXX ( _monthsales, 'Calendar'[Date] ) )
Last non blank value V4: = VAR _monthsales = FILTER ( ADDCOLUMNS ( CALCULATETABLE ( 'Calendar', ALL ( 'Calendar' ), VALUES ( 'Calendar'[Month-Year] ) ), "@sales", [Sales total:] ), NOT ( [@sales] == BLANK () ) ) VAR _lastnonblankdate = MAXX ( _monthsales, 'Calendar'[Date] ) RETURN CALCULATE ( [Sales total:], 'Calendar'[Date] = _lastnonblankdate )
As shown in the above, the result of which measure shows the slowest performance is the same as Q1-performance-analysis.
In order to answer Q3, I tried four different versions of the DAX measures, like below.
Before going into writing measures, I created a table visualization like below in order to compare it with each result.
Last non blank customer V1: = CALCULATE ( LASTNONBLANKVALUE( 'Calendar'[Date], [Customer list:] ), ALL ( 'Calendar' ), VALUES ( 'Calendar'[Month-Year] ) )
Last non blank customer V2: = LASTNONBLANKVALUE( SUMMARIZE ( CALCULATETABLE ( 'Calendar', ALL ( 'Calendar' ), VALUES ( 'Calendar'[Month-Year] ) ), 'Calendar'[Date] ), [Customer list:] )
Last non blank customer V3: = VAR _monthsales = FILTER ( ADDCOLUMNS ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month-Year] = MAX ( 'Calendar'[Month-Year] ) ), "@customerlist", [Customer list:] ), NOT ( [@customerlist] == BLANK () ) ) RETURN CALCULATE ( [Customer list:], 'Calendar'[Date] = MAXX ( _monthsales, 'Calendar'[Date] ) )
Last non blank customer V4: = VAR _monthsales = FILTER ( ADDCOLUMNS ( CALCULATETABLE ( 'Calendar', ALL ( 'Calendar' ), VALUES ( 'Calendar'[Month-Year] ) ), "@customer", [Customer list:] ), NOT ( [@customer] == BLANK () ) ) VAR _lastnonblankdate = MAXX ( _monthsales, 'Calendar'[Date] ) RETURN CALCULATE ( [Customer list:], 'Calendar'[Date] = _lastnonblankdate )
As shown in the above, the result of which measure shows the slowest performance is different than Q1-performance-analysis and Q2-performance-analysis.
If you cannot understand how to properly write a certain DAX function, try it in a different way by using different and simpler DAX functions.
When performance is not good, author DAX formula in a different way by using different DAX functions.
If you cannot get the correct answer, try it in a different way by using different and simpler DAX functions.
And I believe this is one of ways to learn how to write DAX.
I hope this helps to have more fun in authoring DAX in different ways, and to get a good-performing-correct-answer DAX formula.
The sample that I used in this writing is attached.
Comments