Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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).
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)
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).
Proud to be a Super User!
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)
?
...wizardry! Thank you
Last Meal Column = VAR __Index = [Index] RETURN IF(__Index=MAXX(FILTER(ALL(Table1),[Animal]=EARLIER([Animal])),[Index]),1,0)
Small tweak and it worked 😉
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |