Forum Discussion
Last Row Flag Most Current
Hello,
I am trying to flag the row that contains the last time an animal was fed using a Dax Custom Column for splicing a visual.
My data is below: I would like to obtain Last Meal.
The column inputs a 1 for the most recent row entry and if another entry comes in, it is replaced.
Thanks for any help.
| Animal | Food Units Ate | Last Meal |
| Tiger | 5 | 0 |
| Tiger | 3 | 0 |
| Lion | 5 | 0 |
| Lion | 2 | 1 |
| Tiger | 4 | 0 |
| Tiger | 4 | 0 |
| Elephant | 2 | 0 |
| Elephant | 4 | 0 |
| Elephant | 5 | 1 |
| Tiger | 2 | 0 |
| Tiger | 2 | 0 |
| Tiger | 4 | 1 |
| Monkey | 1 | 1 |
Oooh, compared with your last one, this one is trivial:
Last Meal Column = VAR __Index = [Index] RETURN IF(__Index=MAXX(FILTER(ALL(LastMeal),[Animal]=EARLIER([Animal])),[Index]),1,0)
Assumes an Index column like the last solution (Cthulu).
7 Replies
Oooh, compared with your last one, this one is trivial:
Last Meal Column = VAR __Index = [Index] RETURN IF(__Index=MAXX(FILTER(ALL(LastMeal),[Animal]=EARLIER([Animal])),[Index]),1,0)
Assumes an Index column like the last solution (Cthulu).
Darn, I have a caveat that I didn't see,
Animal Food Units Ate Last Meal Tiger 5 0 Tiger 3 0 Lion 5 0 Lion 2 1 Tiger 4 0 Tiger 4 0 Elephant 2 0 Elephant 4 0 Elephant 5 1 Tiger 2 0 Tiger 2 0 Tiger 4 1 Monkey 1 1 Monkey 0 Tiger 0 Elephant 0 Tiger 0 How my dataset is, there are blank values at the bottom that have not occured yet. I thought I could use AllExceptBlank() as a replacement to overcome this but it doesn't work. Otherwise, the last 4 rows are flagged regardless of them not being fed
Maybe:
Last Meal Measure = VAR __Index = MAX([Index]) VAR __Animal = MAX([Animal]) RETURN IF(__Index=MAXX(FILTER(ALL(LastMeal),[Animal]=__Animal && NOT(ISBLANK([Food Units Ate]))),[Index]),1,0)
?
- Hi Greg, just spotted your link to your DAX Cookbook, just got it off that small online store Amazon, look forward to delving into it! Regards David
Here it is as a measure.
Last Meal Measure = VAR __Index = MAX([Index]) VAR __Animal = MAX([Animal]) RETURN IF(__Index=MAXX(FILTER(ALL(LastMeal),[Animal]=__Animal),[Index]),1,0)
