Forum Discussion
Missing "LastNONBLANKVALUE" Dax function.
Hello,
I am trying to find the last value from a table, and was experimenting with LastNonBlank and wasn't getting the correct result and then read some more and realized that I think I should be using LastNonBlankValue, but this Dax function is missing and is not in my latest version of PBI (downloaded January 2022). Update... I have this function in my data sets that are not live (InService). Does it not exist in Service?
My data looks something like:
| Location | ID | Model | Start Date | End Date | Count | Status |
| 1 | 101 | F | 9/9/2021 | 9/24/2021 | 1 | a |
| 1 | 101 | R | 9/25/2021 | 6/30/2022 | 1 | a |
| 1 | 102 | F | 9/9/2021 | 10/1/2021 | 1 | a |
| 1 | 102 | R | 10/2/2021 | 10/30/2021 | 1 | a |
| 1 | 102 | F | 11/1/2021 | 6/30/2022 | 1 | a |
| 2 | 989 | R | 9/9/2021 | 6/30/2022 | 1 | a |
| 3 | 988 | R | 9/9/2021 | 6/30/2022 | 1 | a |
| 4 | 222 | R | 9/9/2021 | 11/17/2021 | 1 | a |
| 4 | 222 | F | 1/4/2022 | 6/30/2022 | 1 | x |
Using the LastNonBlankValue I was hoping to get to the table below so that I could then use this information to filter values in another table,
| Location | ID | Model | Start Date | End Date | Count | Status |
| 1 | 101 | R | 9/25/2021 | 6/30/2022 | 2 | a |
| 1 | 102 | F | 11/1/2021 | 6/30/2022 | 3 | a |
| 2 | 989 | R | 9/9/2021 | 6/30/2022 | 1 | a |
| 3 | 988 | R | 9/9/2021 | 6/30/2022 | 1 | a |
| 4 | 222 | F | 1/4/2022 | 6/30/2022 | 2 | x |
My Measure is currently,
but this only gives me the starting date and does not filter. I assume it is because I'm not looking at the value.
| Location | ID | Model | Start Date | End Date | Count | Status | Last |
| 1 | 101 | F | 9/9/2021 | 9/24/2021 | 1 | a | 9/9/2021 |
| 1 | 101 | R | 9/25/2021 | 6/30/2022 | 1 | a | 9/25/2021 |
| 1 | 102 | F | 9/9/2021 | 10/1/2021 | 1 | a | 9/9/2021 |
| 1 | 102 | R | 10/2/2021 | 10/30/2021 | 1 | a | 10/2/2021 |
| 1 | 102 | F | 11/1/2021 | 6/30/2022 | 1 | a | 11/1/2021 |
| 2 | 989 | R | 9/9/2021 | 6/30/2022 | 1 | a | 9/9/2021 |
| 3 | 988 | R | 9/9/2021 | 6/30/2022 | 1 | a | 9/9/2021 |
| 4 | 222 | R | 9/9/2021 | 11/17/2021 | 1 | a | 9/9/2021 |
| 4 | 222 | F | 1/4/2022 | 6/30/2022 | 1 | x | 1/4/2022 |
I was looking at
@mahoneypa
and DataInsights https://community.powerbi.com/t5/Desktop/How-to-get-the-lastnonblank-value-when-I-need-to-keep-several/td-p/1746240
but can't seem to replicate the answer. Thoughts?
Dear Ashish Mathur,
I am simply in awe! Thank you so much! It is going to take me some time to understand why and how this works. Thank You - Thank You!!
Here I was focusing on the one function and you found such an elegant approach. This is so kind of you. I very much appreciate this support. People like you make this such a wonderful community!
4 Replies
Dear Ashish Mathur,
I am simply in awe! Thank you so much! It is going to take me some time to understand why and how this works. Thank You - Thank You!!
Here I was focusing on the one function and you found such an elegant approach. This is so kind of you. I very much appreciate this support. People like you make this such a wonderful community!
You are welcome. Thank you for your kind words.
Nice solution, Ashish_Mathur. This helped sharpen my understanding of LASTNONBLANK.
In case anyone encounters a performance issue (see link below), here's an alternative approach:
Last Model = CALCULATE ( MIN ( Data[Model] ), Data[Start Date] = MAX ( Data[Start Date] ) )Last Status = CALCULATE ( MIN ( Data[Status] ), Data[Start Date] = MAX ( Data[Start Date] ) )The September 2021 release introduced the use of aggregation functions in CALCULATE filters when they are used in Boolean (True/False) expressions. The expression MAX ( Data[Start Date] ) evaluates in the filter context of the visual, resulting in the appropriate date being used for each Location and ID.
https://www.sqlbi.com/articles/optimizing-lastnonblank-and-lastnonblankvalue-calculations/
