Last Row Flag Most Current

[Anonymous]

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

[Greg_Deckler]

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)

[Greg_Deckler · Accepted solution]

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).

[Daviejoe]

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

[Anonymous]

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

[Greg_Deckler]

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)

?

[Anonymous]

...wizardry! Thank you

[Anonymous]

Last Meal Column = 
VAR __Index = [Index]
RETURN IF(__Index=MAXX(FILTER(ALL(Table1),[Animal]=EARLIER([Animal])),[Index]),1,0)

Small tweak and it worked 😉