Forum Discussion
Impactful IndividualDAX formula for latest text value
Hello All,
I had a previously working DAX formula which worked fine after a lot of testing too, but I am questioning its validity now. I have a table like below. Where the ID column can repeat multiple times, but in my data table visual I want to show ID, and the most recent inserted description and then the date for that description. My DAX was CALCULATE (LASTNONBLANK('TABLE A'[DESCRIPTION],1,FILTER('TABLE A','TABLE A'[INDEX] = MAX('TABLE A'[INDEX])
The index column is the index column I inserted in Power Query.
| ID | DESCRIPTION | INSERT DATE |
| 1 | TOP 1 | 6/5/18 12:00 PM |
| 1 | TOP 2 | 6/5/18 1:00 PM |
| 1 | TOP 3 | 6/5/18 3:00 PM |
| 2 | TEST 1 | 6/7/18 12:00 AM |
| 3 | TEXT 1 | 6/8/18 12:00 AM |
| 4 | HELLO 1 | 6/9/18 12:00 AM |
I want to use a measure and not adopt the previously used techniques of selected 'Latest Insert Date' because that was messing things up for some reason.
Thank You
Hi,
These are the DAX formulas i used
Most recent date = MAX(Data[INSERT DATE])
Most recent description = LOOKUPVALUE(Data[DESCRIPTION],Data[INSERT DATE],[Most recent date],Data[ID],MAX(Data[ID]))
5 Replies
- Ashish_Mathur
Super User
Hi,
These are the DAX formulas i used
Most recent date = MAX(Data[INSERT DATE])
Most recent description = LOOKUPVALUE(Data[DESCRIPTION],Data[INSERT DATE],[Most recent date],Data[ID],MAX(Data[ID]))
nirvana_mokshThanks a lot Ashish, upon doubling checking my DAX formula I found out the reason for my suspicion was because the relationship between the two tables was broken which is why my results on the visulations were so weird. Out of curiosty, did you have a chance to give my DAX formula a look? Just wanted a confirmation if that is still good.
- Ashish_Mathur
I did not try your DAX formula. Did you try mine? Is it working fine?
- nirvana_moksh
I just tried yours and it works perfectly! Just one question, for the part '[Most recent date]' in your DAX formula I substiuted that with MAX(Insert Date) and that worked, but will this work per ID? Meaning will it look at the MAX of Insert Date per ID even if they are more than 1 or will it see MAX of Insert Date for the entire data table of ID's?
- Ashish_Mathur
Yes, it will work per ID. If my reply helped, please mark it as Answer.