Forum Discussion
Return a string with LASTNONBLANK?
I need to build a dashboard in which the last non-blank item in each column is displayed. This is super easy to do for number columns but I can not figure out how to do it for the text columns. Any help would be very much appreciated!
For the number columns I am using this measure, which works great:
Category - Code (most recent) =
LASTNONBLANKVALUE (calendar[Date], SUM( mainTable[Category- Code] ))
But this next measure is as close as I am able to get for the text columns. Unfortunately, the filter by MAX date function does not seem to be doing anything, and the LASTNONBLANK seems to be (if I am understanding correctly) sorting the category by itself (alphabetically?) and giving me the last non blank that starts with a letter closest to the end of the alphabet instead of the one with the latest date. I am stumped.
Category - Name (most recent) =
CALCULATE (
LASTNONBLANK (mainTable[Category - Name], 1 ),
FILTER (
ALLEXCEPT ( mainTable, mainTable [Category - Name] ),
MAX(calendar[Date]
)
))
Thanks for looking and for any advice!
Hi n_b_ - try using
LASTNONBLANKVALUE( Calendar[Date], MAX( maintable[Category - Name] ) )
You should then not need the additional FILTER statement.
Hope this helps
David
7 Replies
Hi n_b_ - try using
LASTNONBLANKVALUE( Calendar[Date], MAX( maintable[Category - Name] ) )
You should then not need the additional FILTER statement.
Hope this helps
David
Thank you so much dedelman_clng! That worked perfectly. That is so much simplier and cleaner than what I was trying to do.
Actually, that seems to have lost the "last non-blank" functionality. It now shows the most recent entry for the column but if there is no entry for todays date for that item, instead of displaying yesterday's entry, it just displays the blank.
Hey! I used this solution to generate the last nonblank value for a column using date. The DAX code is as follows:
LastValue = LASTNONBLANKVALUE(Sheet1[Date Time],MAX(Sheet1[Business Data Value]))The issue I am having is my Date Time column is formatted as "3/14/01 01:30:55 PM" and yet the formula will not retrieve the last non blank value if it is after 12:00pm. Original data was formatted "3/14/01 13:30:55" and I thought switching the Date Time column to this format "3/14/01 01:30:55 PM" would help but I am still only getting the last value before 1:00pm or 13:00. What can I do to fix this?I should also note that any data whose last value is prior to 1:00pm or 13:00 is showing the correct last Business Data Value.Thank you so much!Hi Anonymous - I would suggest you start a new thread with your particular issue, as it will get more views than responding to a post that is already marked "solved". In that new post you should give some sample data and/or your pbix so that the community can have a better look at what you're trying to accomplish.
ALso see this post: How-to-Get-Your-Question-Answered-Quickly for more tips on how to present your issues for fastest resolution.
Hope this helps
David
